2 Replies Latest reply: Aug 4, 2011 4:03 AM by SoreenFourie RSS

    Creating forecast data based on historical data

      Hi

       

      I am new to Qlikview and have the following requirement and need some guidance on how to approach it

       

      The user wants to take historical data (Orders for last 6 months), get the total by day of week (Mon, Tues etc) and then get a daily % of the Weekly Total

      example:

       

      Historical dataSunMonTueWedThurFriSatWeekly total
      Data by resource for last 6 months600500400200507803002830
      % of weekly total21%18%14%7%2%28%11%100%


         The next step in the process will then be to take a weekly forecast number (Note that this is not available by day)  and apply the % calculated above to this number to get a weekly forecast number

      Eg Forcast for week 2800

       

      ForecastSunMonTueWedThurFriSat
      Forecast for week (2800)59449539619849772297


      This will then be used to calculate a daily variance.

      Thanks in advance for help on this

       

      Soreen

        • Re: Creating forecast data based on historical data
          John Witherspoon

          Add a weekday(Date) as DayOfWeek to your table.  Then:

           

          Allocation:
          LOAD
          DayOfWeek
          ,sum(Something) as DaySomething
          RESIDENT SomeTable
          WHERE conditions that narrow you down to the 6 months you want
          GROUP BY DayOfWeek
          ;
          LEFT JOIN (Allocation)
          LOAD sum(DaySomething) as TotalSomething
          RESIDENT Allocation
          ;
          LEFT JOIN (Allocation)
          LOAD
          DayOfWeek
          ,DaySomething / TotalSometing as DayAllocationPercent
          RESIDENT Allocation
          ;

           

          Now I'm not sure what the forecast data looks like, but it's possible you'll be able to do something like this:

           

          LEFT JOIN (Forecast)
          LOAD
          DayOfWeek
          ,DayAllocationPercent
          RESIDENT Allocation
          ;
          LEFT JOIN (Forecast)
          LOAD
          ForecastKeyField
          ,DayOfWeek
          ,WeeklyForecastAmount * DayAllocationPercent as DailyForecastAmount
          RESIDENT Forecast
          ;
          DROP TABLE Allocation
          ;