Rolling 12 months sum

[phoenix]

Hi, I have below dataset with month_end date and Sales. I have to calculate Rolling 12 month sales for each month end.

I have tried

sum(aggr(rangesum(above( total sum({<month_end=>}sales),0,12)),month_end)) , but for 20140131, for past 12 it is picking up values for 20150831,0731.... in a round robin way.

for 20140131 I just want to see rolling 12 mon sum for sales = 100 . Are there any other ways to accomplish Rolling 12 month sum with these month end dates?

month_end	sales
20140131	100
20140228	101
20140331	102
20140430	103
20140531	104
20140630	105
20140731	106
20140831	107
20140930	108
20141031	109
20141130	110
20141231	111
20150131	112
20150228	113
20150331	114
20150430	115
20150531	116
20150630	117
20150731	118
20150831	119

[neelamsaroha157]

try this

Sum({<Date={">=$(=AddMonths(Max(Date), -12))<=$(=Date(Max(Date)))"}>}Sales)

try adding date format in the formula if it doesn't work as it is

[sinanozdemir]

Hi,

Let me know if this is what you are looking for:

Thanks

[phoenix]

Yes, this looks like it works for straight table. But if I sort the month_end to descending it would change the values.

or 20150831 you would see 119 instead of 1362.

but sum(aggr(rangesum(above( total sum({<month_end=>}sum),0,12)),sum)) this will not do that. even with the sort it will show 1362 for 20150831. this works for this simple example for but some reason this expression is giving me wrong results in the qvw.

[phoenix]

I tried this:

in my script i have created month_end_DT variable with date format ->  date#(month_end, 'YYYYMMDD') as RPT_PRD_DT,

and included it in the formula.

Sum({<RPT_PRD_DT={">=$(=AddMonths(Max(RPT_PRD_DT), -11))<=$(=Date(Max(RPT_PRD_DT)))"}>}sum)

this formula is converting to Sum({<RPT_PRD_DT={">=20160131<=20161231"}>}sum) which is correct, but is not calculating rolling sum. I get all zero values.

[mikecrengland]

Try adding a 'relative month' flag to your calendar. Something like:

CASE WHEN DATEDIFF(MONTH,@today, C.FullDate) BETWEEN -12 and 12 THEN DATEDIFF(MONTH,@today, C.FullDate) ELSE NULL END [RelativeMonth]

Then, in your set analysis:

Sum({< [RelativeMonth] = {'>-13<0'}>}sum)

You could do something similar for weeks, quarters, years, etc.

mike

[phoenix]

johnw‌ Could you please help me on this?

[neelamsaroha157]

Check this out. let me know if this is what you are looking for.

(I have taken qvw shared by @shinan).

[phoenix]

I have tried this approach, in the expression it shows correct date range, but it is not adding up sales for those dates.

for 20150831 - . date range in the expression is correct 20140831 to 20150831 and the sum(sales) should be 119+118+.....+108. Instead it only gives 20150831 values. date range in the expression is used to output sum(sales) for the same date, that's the reason we only see values till 20140831, for other dates its zero.

[neelamsaroha157]

Ahhh..ok

You can change the sorting of you dimension and use full Accumulation.

Hope this helps..