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


      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
            Jonathan Dienst

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





                      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


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