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

    Rolling Forecast based on a month

      Team,

       

      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.

       

      Regards,

      Sudha

        • Rolling Forecast based on a month
          Jonathan De Deken

          Hi,


          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

           

          Regards,

           

          Jonathan

           

           

          • Re: Rolling Forecast based on a month
            Anil Babu Samineni

            Sravs,

             

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