Networkdays

Anonymous — Sun, 31 Jan 2016 10:44:30 GMT

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

Re: Networkdays

MK_QSL — Sun, 31 Jan 2016 11:21:18 GMT

You need to create Canonical Calendar.

Check below link.

Canonical Date

Re: Networkdays

Anonymous — Sun, 31 Jan 2016 15:11:17 GMT

Thanks Manish!!

I tried but very hard to understand for me. May be because I dont know much about the Qlik Sense

Can you please guide me by changing the above script?

Thanks in advance!!

Regards

Deepanshu

topic Networkdays in App Development

Networkdays

Re: Networkdays

Re: Networkdays