12 Replies Latest reply: Jun 2, 2017 1:59 PM by Carlos Varela RSS

    Forecast Calculation

    Court van de Lisdonk

      Hello,

       

      Is there a standard procedure for calculating a forecast of sales by extrapolation (accrued revenues diviert by expired months, then multiplied by months left till yearend )

       

      for example: sales till this month November is 2 millions, so every month = 200.000, Extrapolation ist then

      November : 2,2 millions

      December : 2,4 millions

       

      If there is a Standard procedure, can I build it in the loadstatement of my Measure Sales???? 

        • Re: Forecast Calculation
          Marco Wedel

          can you post some different examples and your expected result (best as file)?

           

          thanks

           

          regards

           

          Marco

          • Re: Forecast Calculation
            John Witherspoon

            The attached example has several ways of showing a forecast. These just use chart expressions. If you really need to calculate it in script instead, I could probably figure it out.

            Capture.PNG

              • Re: Forecast Calculation
                John Witherspoon

                Though in your case, it might be something simple like sum(Sales)*count(distinct Month)/12 or something like that. All depends on your data and what you really want to do, I think.

                • Re: Forecast Calculation
                  Court van de Lisdonk

                  Hello John, thank you for your answer and Input .

                   

                  I build it in in my InvoiceSalesmodel. then I discussed it with my Manager and some Users.

                   

                  As always they came with new ideas:

                  The Forcast should predict next 6 months in advance,

                  Input amouts should be the last twelve months, for instance we are now in November so the Input should be from Last year November untill now and the Forecast should go from now (November 2016 ) till May next year.

                   

                  I told them that I wished they figured that out earler. I hope you have some ideas?

                   

                   

                  Thanks in advance

                    • Re: Forecast Calculation
                      John Witherspoon

                      It's been my experience that I rarely get the real requirements until I've already put a QlikView application in front of them. Until they can start interacting with the data, they either don't know or aren't willing to take the time to figure out what they really need in terms of charts and the like. Some of this is my fault, as it was that way for so long that I now by habit just get the most basic of "we want this data" requirements, and then slam out a very basic QlikView with how I might personally want to interact with that data, then solicit feedback. I usually get at least the data model right the first time (I'm usually very familiar with the data), and it's mostly a matter of building additional charts and ways of interacting with the data from there.

                       

                      Anyway, that's all an aside. See the attached for a data model solution to your problem, since it seemed that was your preference. I'm sure it could be solved in the chart too if preferred. Here's the script of interest:

                       

                      CONCATENATE (Data)
                      LOAD
                      monthstart(today(),iterno()-1) as Month
                      ,monthstart(today(),iterno()-1)*Slope+Intercept as Sales
                      ,'Forecast' as Type
                      WHILE iterno() <= 6
                      ;
                      LOAD
                      linest_m(Sales,Month) as Slope
                      ,linest_b(Sales,Month) as Intercept
                      ;
                      LOAD
                      Month
                      ,sum(Sales) as Sales
                      RESIDENT Data
                      WHERE Month >= monthstart(today(),-12)
                      GROUP BY Month
                      ;

                       

                      We're doing a linear regression over the past 12 months of data to predict the future six months of data. We also have types of Actual vs. Forecast, and these are associated with colors loaded in the script and used in the background color expression.

                       

                      The linear trendline in the chart is only to show you that it works, and is based only on the previous 12 months. The trendline doesn't match the forecast when all months are shown, but does match when only the past 12 months and the forecast 6 months are shown.

                      Capture.PNG

                      Capture2.PNG

                    • Re: Forecast Calculation
                      biplab jana

                      Hi John,

                      Need Your help.

                      your solution is perfectly working for Organisation forecasting for future months.

                      But i have some more specific requirement

                      Let i have Monthwise, Branchwise,Product wise granular data of last 24 months in the following format :

                      Branch    Product    Month    SaleAmount

                      A             P01        Jan2016    1000

                      A             P02        Jan2016      500

                      A             P03        Jan2016      800

                      B             P01        Jan2016    1000

                      B             P02        Jan2016      500

                      B             P03        Jan2016      800



                      And i have to forecast "Product wise","Branch wise", "SaleAmount" for futuremonths, i.e. say for month Feb,2017 and March'2017.

                      Can you please help...


                      bj

                        • Re: Forecast Calculation
                          John Witherspoon

                          Just add the dimensions you want to forecast by to the loads where you concatenate the forecast. See attached and script below. Well, this is forecasting 6 months from the past 12, but replace 6 and 12 with whatever you want in this script, and I'd think it would work like you want.

                           

                          CONCATENATE (Data)
                          LOAD
                          Branch
                          ,Product
                          ,monthstart(today(),iterno()-1) as Month
                          ,monthstart(today(),iterno()-1)*Slope+Intercept as Sales
                          ,'Forecast' as Type
                          WHILE iterno() <= 6
                          ;
                          LOAD
                          Branch
                          ,Product
                          ,linest_m(Sales,Month) as Slope
                          ,linest_b(Sales,Month) as Intercept
                          GROUP BY
                          Branch
                          ,Product
                          ;
                          LOAD
                          Branch
                          ,Product
                          ,Month
                          ,sum(Sales) as Sales
                          RESIDENT Data
                          WHERE Month >= monthstart(today(),-12)
                          GROUP BY
                          Branch
                          ,Product
                          ,Month
                          ;

                            • Re: Forecast Calculation
                              biplab jana

                              Hii John,,

                              Thanks for your support.Linear Trend works perfectly with my data

                              Can you please guide to develop the seasonal trend on the basis of the above generated Linear Trend.

                              i.e. for example, seasonal trend of Feb'2017 will be

                              Linear Trend of Feb'2017 + (Actual of Feb'2016 - Linear Trend of Feb'2016)

                              and this is require to generate at Branch,Product level.

                              Thanks in advance..

                                • Re: Forecast Calculation
                                  John Witherspoon

                                  If I've understood, see below and attached. I'm now forecasting the next 12 months instead of next 6, because otherwise we can't visually see the relationship of the seasonal changes to the 12 month trend. You can use any number of months you want, it just makes it hard to visually verify that you have the right results. For a single Branch and Product and 12 months forecasted, you should see each forecasted month be the same distance from the trend line as the same month in the previous year. Well, except that I've assumed we can't have negative sales, so we don't go below 0, even if that's what the seasonal trend suggests.

                                   

                                  Capture.PNG

                                   

                                  Temp:
                                  NOCONCATENATE
                                  LOAD
                                  Branch
                                  ,Product
                                  ,Month
                                  ,sum(Sales) as Sales
                                  RESIDENT Data
                                  WHERE Month >= monthstart(today(),-12)
                                  GROUP BY
                                  Branch
                                  ,Product
                                  ,Month
                                  ;
                                  LEFT JOIN (Temp)
                                  LOAD
                                  Branch
                                  ,Product
                                  ,linest_m(Sales,Month) as Slope
                                  RESIDENT Temp
                                  GROUP BY
                                  Branch
                                  ,Product
                                  ;
                                  CONCATENATE (Data)
                                  LOAD
                                  Branch
                                  ,Product
                                  ,addmonths(Month,12) as Month
                                  ,rangemax(0,Sales+(addmonths(Month,12)-Month)*Slope) as Sales
                                  ,'Forecast' as Type
                                  RESIDENT Temp
                                  ;
                                  DROP TABLE
                                  Temp;