Rolling 3months

[#1]

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.

[shelvinpatel]

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

[Anonymous]

Hi,

Check the below link:

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

[#4]

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.

[#5]

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..

[#6]

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..

[#7]

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

thank you.

[Anonymous]

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)

[sujeetsingh]

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

[#10 · Accepted solution]

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...