Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Another Forecast issue

Hello,

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

DepartmentMonthArticleQuantity
Direct Sales1Shoe23
Direct Sales1Shirt45
Direct Sales1Jeans21
Direct Sales2Shoe56
Direct Sales2Shirt2
Direct Sales2Jeans15
Account Management1Shoe48
Account Management1Shirt456
Account Management1Jeans21
Account Management2Shoe35
Account Management2Shirt45

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

1 Solution

Accepted Solutions
Not applicable
Author

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],
Devil,
[7],
Music,
[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],
Devil,
[7],
Music,
[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

View solution in original post

5 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

Hey Sander,

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

Mike

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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],
Devil,
[7],
Music,
[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],
Devil,
[7],
Music,
[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