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|
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|
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.
How do I achieve this? I been reading all about Set Analysis, but I am not able to crack the code
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?