Time dimension is the most used & common dimension fields in every QlikView application. Whether you have light version master calendar or a full version, you always need to calculate the latest business date based on today or a specific date. As you are aware, there are many ways to calculate the latest business date. Personally, I like the function “FirstWorkDate” – which has three parameters and the third parameter is the optional parameter. We can use this function both in script & expressions.

First Parameter (End date): This parameter is the end date or the threshold date which will be used to calculate the latest business day. For example – this can be today or any future date. The result from the function will always be less than or equal to this parameter.

Second Parameter (Number of workdays): This parameter allows you to specify the number of workdays you wanted to go back. This parameter is not a zero based index instead it starts from “1” which will be same date as the end date (first parameter) if it’s a working date. Assuming today is Monday and if you want previous Friday’s date then you would use “2” as the second parameter.

Third/Optional Parameter (Holidays): This parameter is optional but it’s very useful when you wanted to get the latest business date while accounting any holidays. We can use single quotes around hard-coded dates but the dates should be in same format as application global date format. If the date format is different, then QlikView will ignore the holiday and it will treat as workday. I suggest you to use the variable which holds all the holidays instead of hard-coded holidays.

_______________________________________________________________________________

Example 1:

FirstWorkDate(Today(), 1, ’04-Jul-2014′)

Note: Today is 6th July 2014.

This function returns 03-Jul-2014 because 06-Jul-2014 is Sunday, 05-Jul-2014 is Saturday and 04-Jul-2014 is a holiday.

_______________________________________________________________________________

Example 2:

FirstWorkDate(Today(), 2, ’04-Jul-2014′)

Note: Today is 6th July 2014.

This function returns 02-Jul-2014 because 06-Jul-2014 is Sunday, 05-Jul-2014 is Saturday and 04-Jul-2014 is a holiday. We have specified the second parameter as “2” hence the result is 02-Jul-2014.

_______________________________________________________________________________

Example 3:

If we have a variable vMyHolidays which contains the following string.

’01-JAN-2014′, ’04-JUL-2014′, ’25-DEC-2014′

then we can use the following syntax.

FirstWorkDate(Today(), 1, $(vMyHolidays))

Note: Today is 6th July 2014.

This function returns 03-Jul-2014 because 06-Jul-2014 is Sunday, 05-Jul-2014 is Saturday and 04-Jul-2014 is holiday.

_______________________________________________________________________________