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

    Rolling

    abdulrajaksab tadapatri

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

        • Re: Rolling
          rodrigo silva

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

          • Re: Rolling
            Jonathan Dienst

            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