Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pbower1989
New Contributor

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
Valued Contributor II

Re: Public Holidays and NetWorkHours

Hi Peter,

Please find below link it may help you:

Calculating Holidays in script

Thanks,

Arvind Patil

pbower1989
New Contributor

Re: Public Holidays and NetWorkHours

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

pbower1989
New Contributor

Re: Public Holidays and NetWorkHours

Just wondering if anyone has any ideas?

Thanks

Pete

pbower1989
New Contributor

Re: Public Holidays and NetWorkHours

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