Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hey Sander,
This is the exact thing that I am doing as well! Hope it works out.
Mike
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
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
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