topic Re: Matching forecast with sales in QlikView

Matching forecast with sales

Fri, 15 Aug 2014 16:15:45 GMT

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

Re: Matching forecast with sales

maxgro — Fri, 15 Aug 2014 16:27:55 GMT

see attachment

Re: Matching forecast with sales

Gysbert_Wassenaar — Fri, 15 Aug 2014 16:34:09 GMT

See attached example

Re: Matching forecast with sales

Fri, 15 Aug 2014 16:45:02 GMT

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?

Re: Matching forecast with sales

maxgro — Fri, 15 Aug 2014 16:59:36 GMT

to change a date in year/week

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

Re: Re: Matching forecast with sales

Fri, 15 Aug 2014 18:42:40 GMT

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...)

Re: Matching forecast with sales

MarcoWedel — Fri, 15 Aug 2014 21:59:24 GMT

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

Re: Matching forecast with sales

Fri, 15 Aug 2014 22:14:06 GMT

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?

Re: Matching forecast with sales

MarcoWedel — Fri, 15 Aug 2014 22:43:15 GMT

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

Re: Re: Matching forecast with sales

Sat, 16 Aug 2014 15:12:41 GMT

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.

Re: Re: Re: Matching forecast with sales

MarcoWedel — Sun, 17 Aug 2014 12:05:59 GMT

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

Re: Matching forecast with sales

Sun, 17 Aug 2014 14:18:04 GMT

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?

Re: Matching forecast with sales

MarcoWedel — Sun, 17 Aug 2014 21:41:31 GMT

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

Re: Re: Matching forecast with sales

Sun, 17 Aug 2014 22:48:01 GMT

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