5 Replies Latest reply: Jul 14, 2016 9:26 AM by Jonathan Dienst

# Rolling

how to get rolling of month in given two or more years

• ###### Re: Rolling

(year(end date)- year( start date))*12+ monht(end date)-monht(start date)

• ###### Re: Rolling

thanks,

How to use it with set analysis

• ###### Re: Rolling

As iam looking for set analysis exp

If i want to rolling of three months with the three years as my data Ex:2000,2001,2002,

so if i select Jan in 2001 it should show Jan 2001 Dec2000 Nov2000...

help me out

• ###### Re: Rolling

I suggest that you add a month sequence field to your master calendar. This would be something like:

Calendar:

CalDate,

...

Year(CalDate) * 12 + Month(CalDate) as MonthSequence,

...

(change CalDate to the correct field name for your model)

Now in your set analysis, use MonthSequence and simple arithmetic. This will handle rolling over the year end. Us something like

Sum({<MonthSequence = {"\$(=Max(MonthSequence))"}>} Amount)               // current month

Sum({<MonthSequence = {"\$(=Max(MonthSequence) - 1)"}>} Amount)          // previous month

Sum({<MonthSequence = {"\$(=Max(MonthSequence) - 2)"}>} Amount)          // 2 months prior month

etc

Sum({<MonthSequence = {">=\$(=Max(MonthSequence) - 2)"}>} Amount)      // prior 2 months and current month

etc