Extract Last 3 months from date

[capriconuser]

I have a date Update_Date in this date i have 4 years data like 2016,2017,2018,2019 

i extract year , month and quarter  from this date like this

year(update_Date) as Update_year

month(update_Date) as Update_month

'Q' & Ceil (month(update_date)/3) as Update_quarter 

now i want to extract last 3 months of current year from update_date e.g.

Today is March so from march last 3 months is Dec 2018, Jan 2019 and Feb 2019

So if today is June then last 3 months will be March 2019 , April 2019, May 2019

any solutions?

[shiveshsingh]

Hi

Create variable in script like

vLast3MonthStartDate=Monthstart(Update_Date ,-4);

vLastMonthEndDate=MonthEnd(Update_Date ,-1);

while loading data use variable in where clause

like

Load *

from table

where Update_Date >=$(vLast3MonthStartDate) and Update_Date <=$(vLastMonthEndDate)

You can also use an expression for calculating.

[capriconuser]

in expression i use this Monthstart(Update_Date ,-4); 

or  this

MonthEnd(Update_Date ,-1);

[shiveshsingh]

You can try this expression, plz modify it as required.

=Sum({<Date = {">=$(=Max((Addmonths(Date,-3)))) <$(=Max (Addmonths(Date)))"}>} Sales)

[capriconuser]

will you please explain this expression

[shiveshsingh]

It will give you sum of sales wherein the date is greater than last three months and less than the current month.

[capriconuser]

ok is this possible to add this date expression without sum ?

like from below expression if we remove sum then how we modify it?

=Sum({<Date = {">=$(=Max((Addmonths(Date,-3)))) <$(=Max (Addmonths(Date)))"}>} Sales)