Matching forecast with sales

[#1]

I need some help with figuring out how to display a forecast with older sales. I have future forecasts that I want to display next to sales from the previous year. I have some small files attached. If anyone could help with this, I would appreciate it

Week	Product	Forecast
201431	A	150,000
201430	B	300,000

Week	Product	Sales
201331	A	142,200
201330	B	535,000
201231	A	112,200
201230	B	300,000

I want it to display like this:

Week	Product	Forecast	Sales in 2013	Sales in 2012
201431	A	150,000	142,200	112,200
201430	B	300,000	535,000	300,000

[MarcoWedel]

Hi,

for those sales dates that match the forecast weeks, the solution delivers this result:

As your sample data doesn't contain values for all products in all weeks and all years, there are blank fields in the pivot table though.

hope this helps

regards

Marco

[#3]

I'm having an "allocated memory exceeded" error....

I'm also looking at the expressions and I only see one for amount. How did you do the expressions forecast 2014, sales 2013, etc?

[MarcoWedel]

Hi,

there is only one expression "amount".

The period columns are a dimension dragged to the top like this:

and defined as:

=Dual(Type&' '&Year,Year)

I did not exactly understand your requirement about which dates and weeks are to be compared and thus expected to be in one row.

Can you clarify with further examples?

thanks

regards

Marco

[#5]

Marco,

I've attached a new excel file that includes both forecasts and sales data in the tabs. It's smaller so it's easier to understand when looking at it. The sample layout is what I would like to see in QV. The chart below is a good example of what I want to see but instead of WeekNum, I want to see the Monday date (example: 08/04/2014)  and the forecast and the sales from previous years.

I think we need to convert the sales history field "Week" from Monday date (08/05/2013) in the excel file to the week number which would be week 32 (201332).... so we ignore the 2013 so that we can do a side by side comparison of future forecast vs sales from previous years...

Please let me know if you need any other clarifications. Thank you so much for your help