topic Re: cumulative aggregation over months in QlikView

cumulative aggregation over months

Anonymous — Sun, 09 Apr 2017 05:47:44 GMT

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

Re: cumulative aggregation over months

Anonymous — Sun, 09 Apr 2017 05:50:49 GMT

And I can't keep the sum of last 3 months to be calculated in the script as I need to keep it dynamic

Re: cumulative aggregation over months

vinieme12 — Sun, 09 Apr 2017 09:14:32 GMT

I would recommend using an as of table

Re: cumulative aggregation over months

Anonymous — Sun, 09 Apr 2017 10:07:03 GMT

So do i need to get the 3 months aggregate using sum({<monthdiff="<3">}value) and then use this in the set analysis??

Re: cumulative aggregation over months

sunny_talwar — Sun, 09 Apr 2017 12:58:01 GMT

Are you doing this where MonthYear is your dimension... You might be able to do this

RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 3)) * Avg({<monthstart = {"$(='>=' & Date(MonthStart(Max(monthstart), -11), 'MMM-YYYY') & '<=' & Date(Max(monthstart), 'MMM-YYYY'))"}>} 1)

Re: cumulative aggregation over months

Anonymous — Sun, 09 Apr 2017 13:05:22 GMT

Sunny, Am using month num as dimension and already has 3 other expressions plotted over

Avg({<monthstart = {"$(='>=' & Date(MonthStart(Max(monthstart), -11), 'MMM-YYYY') & '<=' & Date(Max(monthstart), 'MMM-YYYY'))"}>} 1). Giving 12 months . There is a month selected in the filter. Your expression is getting value only for selected month .

Re: cumulative aggregation over months

sunny_talwar — Sun, 09 Apr 2017 13:18:58 GMT

Can you share one of the three expressions (or may be all three) which are working?

Re: cumulative aggregation over months

Anonymous — Mon, 10 Apr 2017 08:07:19 GMT

I have 3 more normal expressions showing data for last 12 months from a selected month. I used month year as dimension and used set analysis like (monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month)))))) ") for these 3 expressions.

Now i need to show the 3 months aggregated value in the same chart.

Please help

Re: cumulative aggregation over months

sunny_talwar — Mon, 10 Apr 2017 08:38:07 GMT

If this has worked for your other expressions

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

Then this should work also.... or share a sample

RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 3)) * Avg({<monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month))))))"}>} 1)

Re: cumulative aggregation over months

Anonymous — Mon, 10 Apr 2017 09:57:07 GMT

Sunny,

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

Year=, Month=, Quarter=,Day=,monthstart={">= $(=num((Max(AddMonths(MonthStart((month)),-11))))) <=$(=num(Max((MonthStart((month)))))) ")

is the complete set am using. (If it makes any difference)

Please let me know if am missing anything

Re: cumulative aggregation over months

sunny_talwar — Mon, 10 Apr 2017 11:07:44 GMT

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

Re: cumulative aggregation over months

Anonymous — Mon, 10 Apr 2017 11:25:38 GMT

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.

Re: cumulative aggregation over months

sunny_talwar — Mon, 10 Apr 2017 11:34:29 GMT

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

Re: cumulative aggregation over months

Anonymous — Mon, 10 Apr 2017 11:38:31 GMT

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

Re: cumulative aggregation over months

sunny_talwar — Mon, 10 Apr 2017 11:43:03 GMT

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

Re: cumulative aggregation over months

Anonymous — Mon, 10 Apr 2017 12:09:53 GMT

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.

Re: cumulative aggregation over months

sunny_talwar — Mon, 10 Apr 2017 12:11:13 GMT

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

Re: cumulative aggregation over months

Anonymous — Mon, 10 Apr 2017 12:26:31 GMT

Sample qvw.

Re: cumulative aggregation over months

sunny_talwar — Mon, 10 Apr 2017 12:34:19 GMT

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)