2 Replies Latest reply: Jan 31, 2016 10:11 AM by Deepanshu Chamoli RSS

    Networkdays

    Deepanshu Chamoli

      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