7 Replies Latest reply: Sep 2, 2014 12:51 PM by Jonathan Poole RSS

    Rolling Forecast Formula

      Hello together,

       

      I Need your help again

       

      It is the MONTH JUNE.

      MonthMonthMonth
      159
      2610
      3711
      4812

      table:

       

      NatureAmountMonth
      ACTUAL3Juni
      FORECAST4Juli
      ACTUAL5Jan
      FORECAST7August

       

      For the first formula I Need the sum of the ACTUAL figures from the JAN to JUN and

      the second formula is the figures of FORECAST from the JULI to DEZ.

       

      1. Formula

      sum(Amount) how get the Sum from JAN to current month JUN into the formula

       

       

      2 Formula

      sum(Amount), how get the SUM from JUL to DEC into the formula.

       

      Thanks

        • Re: Rolling Forecast Formula
          Manish Kachhia

          Provide sample data please...

          • Re: Rolling Forecast Formula
            Jonathan Poole

            It seems this would work.

             

            Actuals:   sum (  {$<Nature={'ACTUAL'}>}  Amount)

             

            Forecast:   sum (  {$<Nature={'FORECAST'}>}  Amount)

              • Re: Rolling Forecast Formula

                but These formulas Shows me the whole amounts, but how can Combine the time Feature inside.

                 

                Actuals Show me the Amount (Actual) from Jan-Jun

                 

                Forecasts Show me the Amount (Forecast) from Jul-Dec.

                 


                  • Re: Rolling Forecast Formula
                    Jonathan Poole

                    Ok . I think you are asking for Jan-Jun numbers for actuals and Jul-Dec numbers for forecast. Because the small data set you provided only has actuals for the jan-jun number and only forecast numbers for the jul-dec numbers the following would work. If your actual data set is different then you should post a representative data set.

                     

                    If you just want to know the syntax to be more explicit with months you can try this. Add as many months in the list as you need.  

                     

                     

                    Actuals:   sum (  {$<Nature={'ACTUAL'}, Month={'Jan','Juni'}>}  Amount)

                     

                    Forecast:   sum (  {$<Nature={'FORECAST'},Month={'Juli','August'}>}  Amount)

                      • Re: Rolling Forecast Formula

                        Thank you for the formulas

                         

                        But how I get the current Date into the Formular

                         

                        Actuals: sum({$<Nature={'ACTUAL'}, Month={'Jan', 'Juni'} Amount)

                         

                        How I get here, that 'Jan' is always the first month and  

                        'Juni' Shows the selected month. And I Need then the 'Jan', 'Feb', 'Mar', 'April', 'Mai', 'Juni'.

                         

                        Forecast: sum({$<Nature={'FORECAST'}, Month={'Juli', 'August'}>} Amount)

                        Juli Shows the currentMonth+1 and August always the December.

                         

                        I hope you understand what I mean.

                         

                        Thanks

                          • Re: Rolling Forecast Formula
                            Jonathan Poole

                            In this example,  Month is a numeric field in the data model from 1 - 12  depending which month.  Current month is Septermber  which is Month=9.

                             

                            If you have a Month number field in your data model you can use this:

                             

                            Forecast:  sum(  {$<Month = {'> $(=num(month(today())))' }>}  Sales)

                            Actuals:   sum(  {$<Month = {'<= $(=num(month(today())))' }>}  Sales)