Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Rolling

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

5 Replies
Not applicable

Re: Rolling

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

Not applicable

Re: Rolling

thanks,

How to use it with set analysis

Not applicable

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

Not applicable

Re: Rolling

=Count( {<D={">=$(=AddMonths(MAX(D),-3))"}>}  D)

MVP
MVP

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

Community Browser