4 Replies Latest reply: Mar 2, 2018 8:47 AM by Jahanzeb Hashmi RSS

    Forecasting Next 6 Months of Items Sold

    Luke Arran

      Hello All

       

      We have created a table which groups Total Items Sold by Month/Year and Stock Code - as demonstrated in the below table. However, we now want to use this historical data to calculate a forecast for the next 6 months of Total Items Sold by generating the values at the script level.

       

      We already have a formula for forecasting but we're open to alternatives - an example of this formula is provided below - with March 2018 being an example of a future month at the time of writing. But I'm not entirely sure how to achieve this in Qlik Script:

       

      • (March 2018) * ((Feb 2018 + Jan 2018 + Dec 2017 + Nov 2017 + Oct 2017 + Sep 2017) / (Feb 2017 + Jan 2017 + Dec 2016 + Nov 2016 + Oct 2016 + Sep 2016))

       

      MonthYearStockCodeTotalItems
      01-2017JEEZ-784325
      02-2017JEEZ-784356
      03-2017JEEZ-784366
      04-2017JEEZ-784315
      05-2017JEEZ-78439
      06-2017JEEZ-78438
      07-2017JEEZ-78436
      08-2017JEEZ-78438
      09-2017JEEZ-784310
      10-2017JEEZ-784319
      11-2017JEEZ-784322
      12-2017JEEZ-784335
      01-2018JEEZ-784332

       

      What is the best scripting solution to get this result, please? Any advice would be great.