Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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?