topic Re: Current rolling 12 months, plus previous rolling 12 months calculation in QlikView

Current rolling 12 months, plus previous rolling 12 months calculation

marcus_steggall — Thu, 21 Jan 2016 11:53:26 GMT

Afternoon all,

Happy new year, hope it was a good break for everyone

I have a query around rolling 12 months. I have seem many calculations around 12 months from today, however I am looking for an additional piece of calculation.

Say I have months from Dec 15 all the way back Jan 13.

Then whenever I select one of those months in the range, I have the calculation to show me the current 12 months, however I also require the previous 12 months from there.

So if I select Dec 15;

Current rolling 12 months - Jan15 to Dec15
Previous rolling 12 months - Jan14 to Dec14

So if I select Nov 15;

Current rolling 12 months - Dec14 to Nov15
Previous rolling 12 months - Dec13 to Nov14

I have the following calculation, which works for the Current rolling 12 months, but can't see to get the Previous one working.

Sum({$<Period_Start_Date={">=$(=MonthStart(AddMonths(Max(Period_Start_Date),-11)))<$(=MonthEnd(Max(Period_Start_Date)))"}>} [Amount_Local])

Any ideas, would be much appreciated.

Re: Current rolling 12 months, plus previous rolling 12 months calculation

Thu, 21 Jan 2016 18:25:50 GMT

You can create Rolling 24 months in the script level.

Please check below document.

Calculating rolling n-period totals, averages or other aggregations

Re: Current rolling 12 months, plus previous rolling 12 months calculation

Thu, 21 Jan 2016 18:29:30 GMT

In your expression make sure that MonthStart function return the same date format like Period_Start_Date.

The other method is create MonthNumber field having 1, 2, 3,4, 5, ....etc for Month ascending order & try below expression.

Sum({Month_Number={">=$(=Max(MonthNumber)-11<=$(=Max(MonthNumber))"}} Amount_Local)