Qlikview Working Day Functions 2

This post continues the short series on how to use the working day functions in Qlikview. (Start from the beginning)

LastWorkDate([start date], n)

This function calculates the date of the nth working day after the start date. The start date is work day number 1 if it is a working day and if the start date is a non-working day, the function will return the next working day for n = 1.

Calculate the date of the nth working day of this month:

=LastWorkDate(MonthStart(Today()), n)

eg LastWorkDate(MonthStart('2010/09/15'), 11) = '2010/09/15', so working day 11 of the month is 15 September 2010.

FirstWorkDate([start date], n)

This function calculates the date of the nth working day before the start date. The start date is work day number 1 if it is a working day and if the start date is a non-working day, the function will return the previous working day for n = 1.

Calculate the date of the nth last working day of this month:

=FirstWorkDate(MonthEnd(Today()), n)

eg FirstWorkDate(MonthEnd('2010/09/15'), 2) = '2010/09/29', so the 2nd last working day of September is the 29th.

Long Form

Both these functions have an optional long form similar to NetWorkDays, namely a list of non-working days to take into account in the calculation. See the earlier post on NetWorkDays for more information.

See the next post on putting it all together.

Qlikview Working Day Functions 1

This post begins a short series on how to use the working day functions in Qlikview.

NetWorkDays (short form)

Calculate the working day number for today:

=NetWorkDays(MonthStart(Today()), Today())

eg NetworkDays(MonthStart('2010/09/15'), '2010/09/15') = 11, so the 15^th is the 11^th working day of September 2010.

Calculate the number of working days in the current month:

=NetWorkDays(MonthStart(Today()), MonthEnd(Today()))

The above examples are the short form of NetWorkDays, which consider working days to be Monday to Friday.

One limitation for people living in regions with calendars different to the standard Western calendar is that there does not appear to be a way to make the basis for the working day calculations to be anything other than Monday to Friday.

NetWorkDays (long form)

The long form of NetWorkDays allows you to take public holidays into consideration. This format adds an arbitrary number of dates to the parameter list which will be considered non-working days, such as:

=NetWorkDays(MonthStart(Today()), Today(), '2010/09/24', '2010/09/25')

This will treat the 24^th and 27^th of September 2010 as non-working days. Note that 25 September 2010 is a Saturday, so is already a non-working day. This is correctly ignored by the NetWorkDay() function.

Using the long form

I have usually used the long form by reading public holidays from a spreadsheet (any data source will do), and concatenating the results into a variable. The script is:

tmpHoliday:
LOAD Date([DATE], 'yyyy/MM/dd') as Date
FROM [..\QVDATA\Public Holidays.xlsx]
(ooxml, embedded labels, table is Sheet1);

tmpConcat:
LOAD concat(chr(39) & Date & chr(39),',') AS HolidayDates
RESIDENT tmpHoliday;

Let vPublicHolidays = fieldvalue('HolidayDates',1);

DROP TABLE tmpHoliday;
DROP TABLE tmpConcat;

Now I can use vPublicHolidays like this:

=NetWorkDays(MonthStart(Today()), Today(), $(vPublicHolidays))

Next article on FirstWorkDate and LastWorkDate