0 Replies Latest reply: Sep 10, 2014 9:57 AM by Samuel Roshan RSS

    Forecast Report - Take Past 3 to 6 Month's data and create a Summary

      Hi All,

       

      I started with QV only few days ago. I am able to import data from Access into QV and show some basic Charts and table.

       

      However now I am faced with the complex stuff. Coming from a SQL Server/Reporting Services background most of the syntax here looks new to me!

       

      We are trying to creating a Forecast Dashboard using historical Forecast Data. Here we got forecast information for a particular month made 6 months in advance. Each month the numbers changes based on new demand. This is how the Raw table looks like, I got sample Forcast data for 4 products. FOrecast is for month of Sep and Oct. Each months were forecasted 3 months in advance.(Eg: Oct'14 as forecasted in Jul , Aug and Sep'14)

      ProductForecastDateForecasted For MonthQty
      AAA1-Sep-141-Oct-14149
      BBB1-Sep-141-Oct-14128
      EEE1-Sep-141-Oct-14128
      DDD1-Sep-141-Oct-14121
      AAA1-Aug-141-Oct-14147
      BBB1-Aug-141-Oct-14118
      EEE1-Aug-141-Oct-14135
      DDD1-Aug-141-Oct-14126
      AAA1-Jul-141-Oct-14122
      BBB1-Jul-141-Oct-14149
      EEE1-Jul-141-Oct-14121
      DDD1-Jul-141-Oct-14104
      AAA1-Aug-141-Sep-14150
      BBB1-Aug-141-Sep-14136
      EEE1-Aug-141-Sep-14147
      DDD1-Aug-141-Sep-14126
      AAA1-Jul-141-Sep-14141
      BBB1-Jul-141-Sep-14147
      EEE1-Jul-141-Sep-14129
      DDD1-Jul-141-Sep-14142
      AAA1-Jun-141-Sep-14143
      BBB1-Jun-141-Sep-14141
      EEE1-Jun-141-Sep-14102
      DDD1-Jun-141-Sep-14118

       

       

      Now using this data I need to create a Summary Table that looks like this:

      MonthProductForecasted For MonthMonth-3Month-2Month-1
      M0AAA1-Oct-14122147149
      M0BBB1-Oct-14149118128
      M0CCC1-Oct-14121135128
      M0DDD1-Oct-14104126121
      M-1AAA1-Sep-14150141143
      M-1BBB1-Sep-14136147141
      M-1CCC1-Sep-14147129102
      M-1DDD1-Sep-14126142

      118

       

      As you can see, it has 4 products , Oct and Sep forecasts made in past three Month. However Oct should show data from Jul, Aug, Sep, whereas Sep'14 should have data from Jun, Jul, Aug. This will go on for the remaining past months.

       

      Question 1:

      How do I achieve this? I been reading all about Set Analysis, but I am not able to crack the code

       

      Question 2:

      The example above has only three months of past data, but my dashboard should be flexible to go back and get upto 8 months of past forecasts.So the number of columns should dynamically grow from 3 to 8 months. Eg: Oct will last 6 month's forecast history from Apr'14 to Sep'14


      Any help on this will be greatly appreciated! I am still using the Personal Edition so might not be able to open your QVWs. For now, can you guys post the actual sccripts and Expressions please?