September 23, 2010

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.

4 comments:

  1. Hi Jonathan
    I 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

    ReplyDelete
    Replies
    1. Not really wierd. Just format that as a date using Date().

      Delete
  2. Well, that’s really great and appreciated. Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. QlikView Training in Hyderabad

    ReplyDelete
  3. It'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