Hi All
I am trying to create a master calendar to calculate the business days in Qlik Sense. But I really miss something which makes my calculation weird.
Please look at it and guide me to correct my error
The issue which I am getting are:
- 1. I have only two rows but master calendar shows three Network days.
- 2. It does not take the holidays in the calculation
Attach is the file which I am using
MainTable:
LOAD
Market,
Division,
Cases,
Forward_to_Ops,
Footprint_Ticket,
DATE(Floor(Open_Date)) as Open_Date,
DATE(Floor(Close_Date)) as Close_Date,
Amount
FROM [lib://Files/Make date - Copy.xlsx]
(ooxml, embedded labels, table is Sheet1);
Temp:
Load
MIN(Open_Date) as minopendate,
MAX(Open_Date) as maxopendate,
MIN(Close_Date) as minclosedate,
MAX(Close_Date) as maxclosedate
Resident MainTable;
LET varMinOpenDate = NUM(PEEK('minopendate',0,'Temp'));
LET varMaxOpenDate = NUM(PEEK('maxopendate',0,'Temp'));
LET varMinCloseDate = NUM(PEEK('minclosedate',0,'Temp'));
LET varMaxCloseDate = NUM(PEEK('maxclosedate',0,'Temp'));
Drop table Temp;
TempCalendar:
LOAD
$(varMinOpenDate)+ IterNo()-1 as NUM1,
$(varMinCloseDate)+ IterNo()-1 as NUM2,
DATE($(varMinOpenDate)+ IterNo()-1) as Temp1,
DATE($(varMinCloseDate)+ IterNo()-1) as Temp2
AutoGenerate 1 while $(varMinOpenDate)+ IterNo()-1 <= $(varMaxOpenDate)
and $(varMinCloseDate)+ IterNo()-1 <= $(varMaxCloseDate);
MasterCalendar:
LOAD
Temp1 as Open_Date,
NetWorkDays(Temp1, Temp2,'12/11/2015', '12/14/2015', '01/12/2016', '01/14/2016') as bddays
Resident TempCalendar
Thanks
Deepanshu
