topic Another Forecast issue in QlikView

Another Forecast issue

Wed, 06 May 2009 18:41:55 GMT

Hello,

I have got a forecast table which contains something like the following data:

Department	Month	Article	Quantity
Direct Sales	1	Shoe	23
Direct Sales	1	Shirt	45
Direct Sales	1	Jeans	21
Direct Sales	2	Shoe	56
Direct Sales	2	Shirt	2
Direct Sales	2	Jeans	15
Account Management	1	Shoe	48
Account Management	1	Shirt	456
Account Management	1	Jeans	21
Account Management	2	Shoe	35
Account Management	2	Shirt	45

I have got a orderdate, article and department to create a link to my sales-data (I have already accomplished this thanks to this forum).

Wish 1: always show all the data from the forecast table, even if a certain article is not sold in the forecasted month by the forecasted department, but still be able to use all the dimensions from the salestable to filter sales. So if I create a table the first 2 columns should contain all forecasted products and their forecast, even if a product isn't sold in the selected period.

Wish 2: The forecast is monthly, as you can see in the table, I would like to make this a daily forecast. This way I can add a calendar which contains all time dimensions including week and day. So I need a way to distribute the forecasted quantity per month to days. Is there a way to handle this in the script?

Hope you guys can give me any pointers!

Regards, Sander

Another Forecast issue

Oleg_Troyansky — Wed, 06 May 2009 23:07:02 GMT

Sander,

well, the "book answer" to the first "wish" is using "loosely coupled tables", but it's such a shaky ground that I wouldn't honestly recommend it... Some other ways of approaching it would be:

1. Keep Sales Date and Forecast Month completely disjointed, i.e. Jan 15 as a Sales Date has nothing in common with Jan as a Forecast Month.

2. Use Set Analysis to apply certain selections and ignore certain other selsctions in a specific expression.

For your Wish 2 - you could use load ... while ... functionality to generate individual dates and calculate daily forecast based on monthly, dividing it by the number of working days in a month (as an example).

Oleg

Another Forecast issue

Wed, 06 May 2009 23:39:04 GMT

Hey Sander,

This is the exact thing that I am doing as well! Hope it works out.

Mike

Another Forecast issue

Tue, 12 May 2009 13:01:37 GMT

Oleg, thanks for your help again! Could you explain the setanalysis functionality in this case a bit more?

Mike, what exactly are you doing? The disjoin or the load...while sequence to generate days from month?

Regards, Sander

Another Forecast issue

Tue, 12 May 2009 13:47:13 GMT

Hi Oleg,

Based on the table above. Could you give me an example of the load..while functionality where months are recalculated to days?

Sorry to bother you so much with this forecasting issue but these are new grounds for me..

Thanks, Sander

Another Forecast issue

Wed, 20 May 2009 13:44:21 GMT

Hi guys,

We've hired QlikView help for this issue. The following script was coded by a QlikView consultant. The script calculates a monthly values to daily values based on the number of working days in the corresponding month.

//********************** Budget for Direct Sales *********************************//
TempBudget:
CROSSTABLE(BudgetMaand, Budget, 1)
LOAD
Artikelcode,
[1],
[2],
[3],
[4],
[5],
,
[7],
,
[9],
[10],
[11],
[12]
FROM [..\..\Datasources\Input\Begroting 2009 Terminalverkopen.xls] (biff, embedded labels, table is DS$);

Budget2:
LOAD
*,
'Direct Sales' as Verkoop.Verkoopafdeling
RESIDENT TempBudget;

Drop table TempBudget;

//********************** Budget for Account Management *********************************//

TempBudget:
CROSSTABLE(BudgetMaand, Budget, 2)
ADD LOAD
Artikelcode,
'Account Management' as Verkoop.Verkoopafdeling,
[1],
[2],
[3],
[4],
[5],
,
[7],
,
[9],
[10],
[11],
[12]
FROM [..\..\Datasources\Input\Begroting 2009 Terminalverkopen.xls] (biff, embedded labels, table is AM$);

Budget3:
LOAD *,
Num#(BudgetMaand) as MonthNum
RESIDENT Budget2;

DROP Table Budget2;

//****************Creation of dates for the budget******************//

LET StartDate = '01-01-2009';

Left JOIN (Budget3)
LOAD
rowno() AS DateKey,
date('$(StartDate)' + recno() - 1, 'MM-DD-YYYY') AS PeriodDate,
Num (Month (date('$(StartDate)' + recno() - 1, 'MM-DD-YYYY'))) as MonthNum
AUTOGENERATE(365);

LEFT JOIN (Budget3)
LOAD
rowno() as MonthNum,
Networkdays ( MonthStart ('1-01-2009', rowno()-1),MonthEnd ('1-01-2009',rowno()-1) )as Nbofworkingdays //Nber of days excluding Saturday and Sunday
AUTOGENERATE (12);

//************** Split of the monthly budget per day *******************//
Budget:
LOAD *,
'0' as DailyBudget,
Num(WeekDay(PeriodDate)) as WeekDay
RESIDENT Budget3
WHERE WeekDay(PeriodDate)>4; //saturdays and sundays

LOAD *,
Budget/Nbofworkingdays as DailyBudget,
Num(WeekDay(PeriodDate)) as WeekDay
RESIDENT Budget3
WHERE WeekDay(PeriodDate)<5; //mondays to fridays

DROP Table Budget3;
DROP Field Budget, BudgetMaand, MonthNum, DateKey, WeekDay FROM Budget;

BudgetNew:
LOAD *,
autonumber (Verkoop.Verkoopafdeling &'-'&Artikelcode&'-'&NUM(PeriodDate)) as Key
RESIDENT Budget;

DROP Table Budget;

Regards, Sander