4 Replies Latest reply: Sep 21, 2017 7:52 PM by Peter Bower RSS

    Public Holidays and NetWorkHours

    Peter Bower

      Hi guys,

      I have 2 questions - one is probably easy, and the second a bit tougher.



      Q1:  I have a calendar where I am importing a distinct list Public Holidays.




      Load Date From [$(vL.QVDSourcePath)Master_MS_Calendar.qvd] (qvd)

      WHERE AustPublicHoliday=1;    --> This produces a list of 87 public holidays (that are valid until the year 2020).

      -->It's in the QVD so I can use the same list of public holidays in multiple applications (and just update the Master Calendar in future).


      However, I then want to use this list in the NetWorkDays function, as an array , so in place of the below:


      networkdays (start_date, end_date [, holiday])  



      I want to have:


      networkdays (start_date, end_date, vPublicHolidays)


      So Question 1 is: How can I pass the holiday values I've loaded into my public holidays table (if it is at all necessary to load them into a table...), and store them into an array vPublicHolidays?


      Ok. Q2:


      Qlik Sense Has a NetWorkDays function. I have to build a valid Cycle Time calculation between 2 days for transactions that are processed for an administration department.


      Their business hours are from 8am-7pm.


      Therefore, I need a 'Business Days' (NetWorkHours) calculation that:

      a)  only accumulates hours between the 11 hours of 8am to 7pm

      b) skips weekends

      c) skips public holidays


      To further explain this - 

      1..total hours in a day is 11; and time is only accumulated between 8-7pm Mon-Fri

      2. if overtime was done on a Saturday, and work was completed. it would count as being completed on the Friday (as an example).


      Any help would be greatly appreciated.