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

[#1]

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)

Product	ForecastDate	Forecasted For Month	Qty
AAA	1-Sep-14	1-Oct-14	149
BBB	1-Sep-14	1-Oct-14	128
EEE	1-Sep-14	1-Oct-14	128
DDD	1-Sep-14	1-Oct-14	121
AAA	1-Aug-14	1-Oct-14	147
BBB	1-Aug-14	1-Oct-14	118
EEE	1-Aug-14	1-Oct-14	135
DDD	1-Aug-14	1-Oct-14	126
AAA	1-Jul-14	1-Oct-14	122
BBB	1-Jul-14	1-Oct-14	149
EEE	1-Jul-14	1-Oct-14	121
DDD	1-Jul-14	1-Oct-14	104
AAA	1-Aug-14	1-Sep-14	150
BBB	1-Aug-14	1-Sep-14	136
EEE	1-Aug-14	1-Sep-14	147
DDD	1-Aug-14	1-Sep-14	126
AAA	1-Jul-14	1-Sep-14	141
BBB	1-Jul-14	1-Sep-14	147
EEE	1-Jul-14	1-Sep-14	129
DDD	1-Jul-14	1-Sep-14	142
AAA	1-Jun-14	1-Sep-14	143
BBB	1-Jun-14	1-Sep-14	141
EEE	1-Jun-14	1-Sep-14	102
DDD	1-Jun-14	1-Sep-14	118

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

Month	Product	Forecasted For Month	Month-3	Month-2	Month-1
M0	AAA	1-Oct-14	122	147	149
M0	BBB	1-Oct-14	149	118	128
M0	CCC	1-Oct-14	121	135	128
M0	DDD	1-Oct-14	104	126	121
M-1	AAA	1-Sep-14	150	141	143
M-1	BBB	1-Sep-14	136	147	141
M-1	CCC	1-Sep-14	147	129	102
M-1	DDD	1-Sep-14	126	142	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?