Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
see the attached file for rolling 12 month
replace 13 with 12,6 and3 for rolloing 12,6,3 in expression
hope this helps