topic Extract Last 3 months from date in QlikView

Extract Last 3 months from date

capriconuser — Mon, 04 Mar 2019 07:23:13 GMT

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 — Mon, 04 Mar 2019 07:33:08 GMT

Create variable in script like

vLast3MonthStartDate=Monthstart(Update_Date ,-4);

vLastMonthEndDate=MonthEnd(Update_Date ,-1);

while loading data use variable in where clause

Load *

from table

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

You can also use an expression for calculating.

capriconuser — Mon, 04 Mar 2019 07:38:23 GMT

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

or this

MonthEnd(Update_Date ,-1);

shiveshsingh — Mon, 04 Mar 2019 07:39:08 GMT

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

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

capriconuser — Mon, 04 Mar 2019 10:24:38 GMT

will you please explain this expression

shiveshsingh — Tue, 05 Mar 2019 11:09:47 GMT

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

capriconuser — Wed, 06 Mar 2019 06:18:07 GMT

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)