Accounting for public holidays with NetWorkDays()

Order Delivery Lead Days NetWorkDays() used to do some basic Delivery Lead Days calculations, excluding weekends.

NetWorkDays() is a script or chart function that returns the number of work days between two dates. This is useful for calculating, for example, delivery leads times, support call turnaround times or any other metric where you want the number of working days between two dates. NetWorkDays(date1, date2) on its own, however does not take public holidays into account. To include public holidays you need to add a third argument to the function, e.g., NetWorkDays(date1, date2, '25-Dec-2012') to remove Christmas Day from the calculation.

To include a number of public holidays, you can list them in a variable and then use that variable in the NetWorkDays() function. I've put together a string of all of the public holidays from 2011 to 2014.

Step 1: Create a variable.

set vPublicHolidays = '01-Jan-2011', '21-Mar-2011', '22-Apr-2011', '25-Apr-2011', '27-Apr-2011', '02-May-2011', '16-Jun-2011', '09-Aug-2011', '24-Sep-2011', '16-Dec-2011', '26-Dec-2011', '27-Dec-2011', '02-Jan-2012', '21-Mar-2012', '06-Apr-2012', '09-Apr-2012', '27-Apr-2012', '01-May-2012', '09-Aug-2012', '24-Sep-2012', '17-Dec-2012', '25-Dec-2012', '26-Dec-2012', '01-Jan-2013', '21-Mar-2013', '29-Mar-2013', '01-Apr-2013', '27-Apr-2013', '01-May-2013', '16-Jun-2013', '09-Aug-2013', '24-Sep-2013', '16-Dec-2013', '25-Dec-2013', '26-Dec-2013', '01-Jan-2014', '02-Jan-2014', '21-Mar-2014', '18-Apr-2014', '21-Apr-2014', '27-Apr-2014', '01-May-2014', '16-Jun-2014', '09-Aug-2014', '24-Sep-2014', '16-Dec-2014', '25-Dec-2014', '26-Dec-2014';

Step 2: Use that in your script or chart functions.

e.g., NetWorkDays([Order Date], [Delivery Date], $(vPublicHolidays)) as [Order Lead Time],

I've compiled that list manually from the internet, the next step would be to automate the process of pulling in the public holiday dates and stringing them into the variable, so that the list stays relevant from year to year.

Posted by Colin Hancox

Senior QlikView Consultant. Currently providing Business Intelligence solutions for various large companies in the retail and manufacturing sectors, using QlikView on Oracle and Microsoft AX databases.