Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
brindlogcool
Creator III
Creator III

Rolling 3, 6 and 12 months calculation

Hi,

I want to claculate the rolling 3, 6 and 12 months in such a way that it should display 3 months,6 months and 12 months.

Like for rolling 3 months i should display the 3 individual months, for 6 months i should display 6 individual months and for 12  months i should display 12 individual months.

How to get this each month . In set analysis we could sum the last 12 months, 6 months or QTD. I have the master calendar which will give me ytd,qtd

2 Replies
Not applicable

I generally do this by creating a MonthIndex for each month and then using variables refering to these MonthIndexes in a set expression.

Create the MonthIndex when doing the calender by making a field like this:

Year * 12 + num(Month) as MonthIndex.  This will give you a unique index for each month in the system (You could use a month count instead but I find the index to be more accurate especially if there are missing months.

Then create some variables:

vMaxMonthIndex = Max(MonthIndex)

vMaxMonthIndex3Months = Max(MonthIndex) - 2

vMaxMonthIndex6Months = Max(MonthIndex) - 5

vMaxMonthIndex12Months = Max(MonthIndex)-11

Then use these in your set expressions:

3Months:

=sum({<MonthIndex={'>=vMaxMonthIndex3Months'} {'<=vMaxMonthIndex'} >} FieldToSum)

6Months:

=sum({<MonthIndex={'>=vMaxMonthIndex6Months'} {'<=vMaxMonthIndex'} >} FieldToSum)

12Months:

=sum({<MonthIndex={'>=vMaxMonthIndex12Months'} {'<=vMaxMonthIndex'} >} FieldToSum)

Syntax may not be 100% correct...

Also note you may need to tweak them if you want to leave out the current incompleted month or add some ignore field satements depending on how users will use or interpret the chart.

SunilChauhan
Champion
Champion

see the attached file for rolling 12 month

replace 13 with 12,6 and3 for  rolloing 12,6,3 in expression

hope this helps

Sunil Chauhan