September 16, 2010

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 15th is the 11th 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 24th and 27th 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

4 comments:

  1. Dear Jonathan,

    When I reload the script it give me an error:

    Error in expression:
    CONCAT is not a valid function

    What could be wrong? I'm using Qlikview version 7 at the moment.
    I couldn't find CONCAT function on help either.

    Frenky

    I had change a bit the function on script as follow:

    tmpHoliday:
    LOAD Date([DATE], 'dd-MM-yyyy') as WorkingDate
    FROM [%HOMEDRIVE%%HOMEPATH%\Excel\PublicHolidays.csv] (ansi, txt, delimiter is ',', embedded labels);

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

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

    DROP TABLE tmpHoliday;
    DROP TABLE tmpConcat;

    ReplyDelete
  2. Frank

    I am afraid that the concat function is valid in V9, but apparently not in V7. I suggest that you consider upgrading to a more recent version.

    Jonathan

    ReplyDelete
  3. our way of explanation of QlikView is very fantastic.you mention in article lot of information varies types of qlikview data, thanks for given information.well done keep it up and we are providing QlikView Training in Hyderabad on all modules latest courses.

    ReplyDelete
  4. Dear Jonathan,

    I have some problems with my set analysis when use firstworkdate with list Holiday.

    My Holiday list is Field (workdate) which include by 1 and 0. if 0 is Holiday.

    I want to apply this into Firstworkdate function but not work. This is my analysis.

    firstworkdate(today(),6,workdate=1)

    I have tried to make variable $(v_holiday) or use concat function but they didnt work with me.

    Thanks for your consider and reply !

    ReplyDelete