2 Replies Latest reply: Jul 4, 2016 12:55 PM by Anil Samineni RSS

    Rolling Forecast based on a month



      Below is the requirement I am working on,

      Rolling Average By 3 months, 6 months and 12 months


      Need to comapre the current year rolling values with Previous Year rolling values based on month chosen.


      If I have selected April 2011


      Months             Previous yr                 Current year

      3months        Jan-Mar Sales                    Jan-Mar Sales

                               of 2010                         of 2011

      6months          Jan-Mar Sales(2010)               Jan-Mar Sales(2011)

                          + Oct - Dec(2009)                       + Oct - Dec(2010) 



      The expression I am trying to use for Prev Yr is    


      Sum({<Month = {"<=$(=max(Month))"},

           Year = {$(=(max(Year)-1))}  >}Quantity)


      and for current year is


      Sum({<Month = {"<=$(=max(Month))"},

           Year = {$(=(max(Year)))}  >}Quantity)



      How is it possible to specify the range of the months?


      I tried for 3 months for prev yr as below . But it brought 0 values.

      Sum({<Month = {"<=$(=max(Month, -3))"},

           Year = {$(=(max(Year)-1))}  >}Quantity)



      Please advise.




        • Rolling Forecast based on a month
          Jonathan De Deken


          Why don't you set the months as selections (you need them as field in the model via a calendar for example) and then declare 2 variables CurrentYear and PreviousYear which you can use in set analyses:


          CurrentYear =  Year(Today())

          PreviousYear =  Year(Today()) - 1


          Then in the expression:

          Expr1_CurrentY = Sum({$<(Year = {$(CurrentYear)}>} Quantity)

          Expr1_PrevY = Sum({$<(Year = {$(PreviousYear)}>} Quantity)


          Then what ever months you select it will compare this year with previous year







          • Re: Rolling Forecast based on a month
            Anil Samineni



            I have same BRD, If possible - Can you please share me that qvw or code.