Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
brindlogcool
Contributor 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

Re: Rolling 3, 6 and 12 months calculation

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
Esteemed Contributor

Re: Rolling 3, 6 and 12 months calculation

see the attached file for rolling 12 month

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

hope this helps