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

[Anonymous]

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

[vinieme12]

I would recommend using an as of table

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

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 .

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

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