Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Public Holidays and NetWorkHours

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

4 Replies
arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Peter,

Please find below link it may help you:

Calculating Holidays in script

Thanks,

Arvind Patil

Anonymous
Not applicable
Author

Hi there,

Thanks for the response.

This is helpful however as per the question am looking for calculation that correctly accounts for Business Hours


Thanks

Peter

Anonymous
Not applicable
Author

Just wondering if anyone has any ideas?

Thanks

Pete

Anonymous
Not applicable
Author

For anyone interested, the answer was here Business Working Hours Calculation