cumulative aggregation over months

[Anonymous]

Hi All,

I need to the show the cumulative sums of last 3 months over last 12 months.

For example, iIf I select July 2016, I need to plot the sum(last 3 months may, June, July) of each month over a period of 12 months (Like we did for July.)

Hope am clear.

I tried using the month as dimension and using set analysis get the sum() of 3 months using

sum(aggr(rangesum(above(total sum({<Month=>}amount),0,3)),Month)),

and then trying to do set analysis something like (monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month)))))) "). But its give allocated memory exceeded error.

Please help

[sunny_talwar]

If we don't keep the filter on month start in Rangesum() part its taking all the month into consideration.

Yes that is what we want.... we want to ignore selections so that RangeSum can show you 3 month Sum for all the dates....

RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 3))

This will always show everything regardless of your selection in Month of monthstart field....

But, this expression

Avg({<monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month))))))"}>} 1)

Will only show 1 when your are within the 12 month from the month or date selected and will be 0 for all other periods. So, for the 12 months you want to see, you will have this

RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 3)) * 1

For all other months, you will get this

RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 3)) * 0 = 0

Does this help? If it still doesn,t help, then I would suggest you to provide a sample and the expected output

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[Anonymous]

Sunny, Expression is working fine and giving correct values. But It's not just displaying the 12 months. It's displaying from the first month onwards with 0 as values. I checked suppress zero values and suppress missing values too. But

still am getting zeros.

[sunny_talwar]

That might be issue with QlikView version then... what version are you using? Can you upgrade?

[Anonymous]

Qlikview 11.20 SR13. I won't be able to upgrade. .Isn't there any way to implement this in the specified version??

[sunny_talwar · Accepted solution]

May be with calculated dimension

If(MonthYear >= AddMonths(Max(TOTAL MonthYear), -12) and MonthYear <= Max(TOTAL MonthYear), MonthYear)

Expression

RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 3))

[Anonymous]

Sunny,

It doesn't consider the values of the months before 12 months. Averages of 10th and 11 months from the selected month are incorrect.

[sunny_talwar]

Can you mock up some data for me and show me the issue?

[Anonymous]

Sample qvw.

[sunny_talwar]

Shiva -  I am going to have a tough time helping you brother... Do you have Month field or MonthYear field? AddMonths to a Month field

Fixing the dimension, and a slight change to the expression seems to be working (but will give you issues if you MonthYears are not sorted correctly)