topic Re: Help me to get only last 3 months data dynamically in QlikView

Help me to get only last 3 months data dynamically

Thu, 09 Jun 2016 04:03:31 GMT

HHi all

i I have 2015 (Oct,Nov,Dec) and 2016(Jan,Feb) data, so I need to display only last 3 months data (Dec 2015, Jan 2016, Feb 2016).

I tried with set analysis by using below mentioned expression

Sum({1<Month={">=$(=MaxString(Month) -2) <=$(=MaxString(Month))"}>Sales)

but the above condition fetch the data only 2015 only.

HHelp me to solve

THanks

AR UMAMAHESH

‌

Re: Help me to get only last 3 months data dynamically

Thu, 09 Jun 2016 04:31:21 GMT

Hi,

try this,

=sum({1<Date={'>=$(=(Num(MonthStart(Max(DateField), -2))))<=$(=(Num(MonthEnd(Max(DateField)))))'}>}Sales)

-Joyson G

Re: Help me to get only last 3 months data dynamically

Thu, 09 Jun 2016 05:29:50 GMT

Hi all

i I have 2015 (Oct,Nov,Dec) and 2016(Jan,Feb) data, so I need to display only last 3 months data (Dec 2015, Jan 2016, Feb 2016).

I tried with set analysis by using below mentioned expression

Sum({1<Month={">=$(=MaxString(Month) -2) <=$(=MaxString(Month))"}>Sales)

but the above condition fetch the data 2015 data only.

If I use max(year) it will fetch only max year data. Ie 2016

Help me to solve this

THanks

AR UMAMAHESH

Re: Help me to get only last 3 months data dynamically

vikasmahajan — Thu, 09 Jun 2016 05:37:28 GMT

Hi,

Sometimes there may be requirements from users where they want to see the charts in from certain point of time like YTD, QTD, MTD, Last 5 Years etc., please find the set analysis expressions for this type of scenarios.

YTD Sales (Year To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Today()))<=$(=Today())’}>} Sales )

Note: Year=, Quarter=, Month=, Week= excludes the selections in Year, Quarter, Month and Week dimensions.

QTD Sales (Quarter To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Today()))<=$(=Today())’}>} Sales)

MTD Sales (Month To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today()))<=$(=Today())’}>} Sales)

WTD Sales (Week To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Today()))<=$(=Today())’}>} Sales)

Last 5 Years Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Today(), -4))<=$(=Today())’}>} Sales )

Last 6 Quarters Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Today(), -5))<=$(=Today())’}>} Sales )

Last 12 Months Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today(), -11))<=$(=Today())’}>} Sales )

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Today(), -14))<=$(=Today())’}>} Sales )

Last 10 Days Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Today()-9))<=$(=Today())’}>} Sales )

Yesterday Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘$(=Date(Today()-1))’}>} Sales )

Hope this helps you

Vikas

Re: Help me to get only last 3 months data dynamically

senpradip007 — Thu, 09 Jun 2016 05:56:21 GMT

Use this expression

sum({<Date={">=$(=addmonths(Max(Date), -2)) <=$(=Max(Date))"}>} Data)

Re: Help me to get only last 3 months data dynamically

susovan — Thu, 09 Jun 2016 06:36:24 GMT

Try this attachment,

Re: Help me to get only last 3 months data dynamically

Mon, 13 Jun 2016 10:20:48 GMT

‌Thanks Vikas,

I followed the above scenarios, finally I got the result

THanks,

AR UMAMAHESH

Re: Help me to get only last 3 months data dynamically

Mon, 13 Jun 2016 10:37:16 GMT

‌HI

The below is the dynamic way to get last 3 months data

First we need to create a variable to get MaxDate from the data then use that variable in the set analysis as mentioned below.

sum({<as_of_dt={">=$(=MaxDate,-3)) <=$(=MaxDate)"}>}sales)

THanks,

AR UMAMAHESH