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

[maxgro]

see attachment

[Gysbert_Wassenaar]

See attached example

[#4]

This is almost perfect!!! I do have one issue though.. all my sales are coming in as the monday dates... so instead of 2014/32 .. it shows up as 8/4/2014..

What is a way to go about this?

[maxgro]

to change a date in   year/week

=Year(yourdatefield) & '/' & num(Week(yourdatefield), '00')

[#6]

I'm having trouble changing the dates and then making it work. Can you make an example using the new sample sales file?

Also - since the dates for this year and last year won't match up... what can we do about it? is there any way we can add 1+ day ? (i.e 8/4/2014 is a Monday but 8/4/2013 is a Sunday...)

[MarcoWedel]

Hi,

one more possible solution:

tabForecastSales:
LOAD WeekName(MakeWeekDate(Left(@1,4), Right(@1, Len(@1)-4))) as Week,
    Right(@1, Len(@1)-4) as WeekNum,
    Left(@1, 4) as Year,
    @2 as Product,
    Money(@3,'#,##0 €;-#,##0 €') as Amount,
    'Forecast' as Type
FROM [http://community.qlik.com/servlet/JiveServlet/download/589519-120409/forecast%20sample.xls]
(biff, no labels, table is Sheet1$);

LOAD WeekName(Week) as Week,
    Week(Week) as WeekNum,
    Year(Week) as Year,
    Product,
    Money(Sales,'#,##0 €;-#,##0 €') as Amount,
    'Sales' as Type
//FROM [http://community.qlik.com/servlet/JiveServlet/download/589519-120408/sales%20sample.xls]
//(biff, embedded labels, table is Sheet1$);
//
//Inline Load due to wrong years in sample data
INLINE [
    Week, Product, Sales
    08/04/2013, A, 142200
    07/28/2013, B, 535000
    08/04/2012, A, 112200
    07/28/2012, B, 300000
];

hope this helps

regards

Marco

[#8]

Does this address the date issue?

This year and last year won't match up... what can we do about it? Is there any way we can add 1+ day ?

(i.e Week 32 - 8/4/2014 is a Monday but 8/4/2013 is a Sunday...so I would need 8/5 for year 2013 and 8/6 for 2012 in order for the weeks to match up, that is be Week 32.

Does this make sense?

[MarcoWedel]

I understood, that you compare equal week numbers across multiple years while getting your sales data per date (mondays) not per week.

It's due to your sample data, that 8/4/14, 8/5/13 and 8/6/12 are not in the same week number und thus can't be compared.

Maybe you can extend your sample data to clarifiy?

regards

Marco

[#10]

Data set is now fixed to help with this particular problem. Thank you, your help is appreciated

Note: Forecast is by week numbers and Sales is by Monday dates.