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.
Hi Jonathan
ReplyDeleteI was looking into your working day function and i tried to use the LastWorkDate(MonthStart('01.06.2014'),6) i got a weird output 41799. Any idea why i am getting this weird output?.
It would rather return 09.06.2014.
Thanks in Advance.
Vineet
Not really wierd. Just format that as a date using Date().
DeleteIt's actually a cool and useful piece of info. I am glad that you simply shared this useful info with us. Please keep us up to date like this. Thanks for sharing. QlikView Training in Hyderabad
ReplyDelete