Last day of prev month where it has value.

[suvechha_b]

Hi All ,

How to get the Last day of previous month where it has value . say for example. I did get the last day of previous month but i.e 31st of aug. but there is no data for 31st aug. the last day is 30 th of aug where it got day.

How can I pick up the last day of prev month where we have data.

My script in under "Flagfields" : 

F if("Statement Date" = Date(Monthend(Addmonths('$(MAX_STATEMENT_DATE)',-1)), 'YYYY-MM-DD'), 'Opening Balance '& Day(Monthend(Addmonths('$(MAX_STATEMENT_DATE)',-1)) ) & Month(addmonths('$(MAX_STATEMENT_DATE)',-1)),

sample model attached.

[StarinieriG]

Hi,

if you want to do this inside the script you could do something like this:

1) Where you load the table with all the dates, create two new fields:

ex.

Month(AddMonths(date,-1)) & Year(AddMonths(date,-1)) as LastMonth

Month(date) & Year(date) as Month

2) Then resident the table and use join

ex.

left join

Load

Month as LastMonth,

max(date) as max_date_last_month

Resident Table

group by Month;