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.

       

      e.g.

      PublicHolidays:

      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.

       

       

      Thanks

       

      Peter