Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
marcus_steggall
Creator
Creator

Current rolling 12 months, plus previous rolling 12 months calculation

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.

2 Replies
Not applicable

You can create Rolling 24 months in the script level.

Please check below document.

Calculating rolling n-period totals, averages or other aggregations

Not applicable

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)