topic Rolling 3months in QlikView

Rolling 3months

Fri, 23 Jan 2015 14:50:01 GMT

Hi QV,

I want to make a Bar chart For total amount in for last 3months.
For example if user selects Report_Month= 1214 then i should show 3bars for 1214,1114,1014.
I created 2 other fields like Prev_Report_Month1 & Prev_Report_Month2 in script - which calculates the Previous MonthYear values.

In chart i Used below expressions.
dimension : Report_Month (1214,1114,1014,0914 etc..)
Expression1:Sum(AMOUNT)
Expression2: sum({<Report_Month={"$(=num(Only(Prev_Report_Month1),'0'))"}>}AMOUNT)
Expression3: sum({<Report_Month={"$(=num(Only(Prev_Report_Month2),'0'))"}>}AMOUNT)

But i am getting "0" for both Expression2 & 3.

Re: Rolling 3months

shelvinpatel — Fri, 23 Jan 2015 15:38:58 GMT

Hi Jhansi,

You won't need additional three columns or separate three expressions. See attached example by simply adding a data field associated with each Report_Month value, which will be also useful in sorting unless you already have a calendar table.

Thanks, Shelvin

Re: Rolling 3months

Anonymous — Fri, 23 Jan 2015 15:50:45 GMT

Hi,

Check the below link:

http://community.qlik.com/docs/DOC-4252

Re: Rolling 3months

Fri, 23 Jan 2015 16:29:14 GMT

Hi Jhansi,

Create three expressions,

First one,

Sum({<Month={“$(=Max(Month))”}>}Value)

Second one,

Sum({<Month={“$(=Max(Month)-1)”}>}Value)

Third one,

Sum({<Month={“$(=Max(Month)-2)”}>}Value)

Hope this clarifies.

Re: Rolling 3months

Fri, 23 Jan 2015 18:27:28 GMT

I dont have the Column Date(or Month Start etc..), I only relay on Report Month... If i am going to create a Date, then i should create from 1st to 31st.. but i only use Month Start in that, will this be good practise? Ofcourse it is because once in a year we will update the Calendar..

Re: Rolling 3months

Fri, 23 Jan 2015 18:29:46 GMT

Report Month values are like 1014, 1114,1214,0115 etc.. So we cant just write Max(Month)-1

these give values 1013,1113,1213,0114 etc.. which are irrelavent..

Re: Rolling 3months

Fri, 23 Jan 2015 18:36:26 GMT

This post is to calculate over all total amount in one bar for all 3months. but i need seperate bars.

thank you.

Re: Rolling 3months

Anonymous — Fri, 23 Jan 2015 18:51:57 GMT

Hi,

Try below 3 expressions:

Sum({<Month={“$(=Max(Month(Date#(ReportMonth,'MM/YY'))))”}>}Value)

Sum({<Month={“$(=Max(Month(Date#(ReportMonth,'MM/YY'))))-1)”}>}Value)

Sum({<Month={“$(=Max(Month(Date#(ReportMonth,'MM/YY'))))-2)”}>}Value)

Re: Rolling 3months

sujeetsingh — Fri, 23 Jan 2015 18:53:18 GMT

I will recommend you to use a variable which will have value of the max month of max year by default.

Or if any month selected then the month selected one.

Now for other two rolling months you need to use set analysis in your expressions as month variable-1 and month variable -2

Re: Rolling 3months

Fri, 23 Jan 2015 19:57:15 GMT

I can able to get the result bu using these expressions

dimension : Report_Month (1214,1114,1014,0914 etc..)
Expression1:Sum(AMOUNT)
Expression2: sum({1<Report_Month={"$(=num(Only(Prev_Report_Month1),'0'))"}>}AMOUNT)
Expression3: sum({1<Report_Month={"$(=num(Only(Prev_Report_Month2),'0'))"}>}AMOUNT)
I am trying to pull Report_Month which is apart from current selections. So We should use "1" in set analysis to avoid current selections.
Thank you for all your suggestions...

Re: Rolling 3months

Fri, 23 Jan 2015 19:57:57 GMT

Yes. This is one way of doing..

Thank you.