October 10, 2010

Qlikview Working Day Functions 3

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

Putting it together (IsWorkDay subroutine)

The code below is a subroutine, callable from the load script, that evaluates a date and return true if it is a work day, and false if it is not.

Parameters:
  • testDate (in, date) - the date to test
  • rval (out, true/false) - true if the date is a work date, false otherwise.
Variable vPublicHolidays is a string containing a comma separated list of public holidays. See the earlier post for more information.


Sub IsWorkDay(rval, testDate)
Let zTest = NetWorkDays(MonthStart(testDate), testDate,
$(vPublicHolidays));

// If the day is a non-working day, and there are no
// working days before it in the current month,
// NetWorkDays will return 0

If zTest = 0 Then
rval = false();

// If the day is a working day, and/or there are working
// days before it, NetWorkDays will return > 0
Else
// If the date is the first day of month then it must
// be a work day

If DayStart(testDate) = MonthStart(testDate) Then
rval = true();
// Else compare with NetWorkDay for yesterday. If
// different, then the date is a work day
Else
rval = If((zTest <> NetWorkDays(MonthStart(testDate),
testDate-1, $(vPublicHolidays))),
true(), false());
End If
End If
End Sub;

Put this script on a separate tab on your load script, or in an include file. Then call it like this:


Let vTheDate = Date#('2010/09/22', 'YYYY/MM/DD');
Call IsWorkDay(vTheDate, rval);
If rval Then
... do something if it is a work date
Else
... do something if it is not a work date
End If

The series start page is here.