Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

deepanshu_chamo1
New Contributor

Networkdays

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. 1. I have only two rows but master calendar shows three Network days.
  2. 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

Tags (1)
2 Replies

Re: Networkdays

You need to create Canonical Calendar.

Check below link.

Canonical Date

deepanshu_chamo1
New Contributor

Re: Networkdays

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