Calculate working days (expression that takes account of weekend and bank holidays)

[#1]

Hi all,

We have to monitor customer complaints performance on a regular basis. 1 KPI is number of working days to respond.

We are looking to do this calcualtion on Qlikvew as our current compliats app is already used to do lots of other analysis on complaints.

Is there an expression/calculation that can be used which would do this (importantly taking account of both weeends and bank holidays).

The calculation is the difference between the 'start date' and 'completion date' .

Any help would be appreciated.

Chris

[Anonymous]

tyou can use the function networkdays. I use it for my customer too

One (1) Holiday calendar may be used.

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9 

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8 

[amit_saini]

Chris,

Check:

Re: Date Difference Excluding weekends

Thanks,
AS

[amit_saini]

Also:

https://community.qlik.com/thread/13984

http://yahqblog.blogspot.nl/2010/09/qlikview-working-day-functions.html

Thanks,

AS

[jonathandienst]

The NetWorkDays() function calculates the working days between two dates. It considers workdays to be Mondays - Dridays (not adjustable). It optionally accepts a list of dates as holidays that it excludes from the count.

     =NetWorkDays(ClosedDate, OpenedDate)

If you have a field containing holidays, then create a list in script:

     vHolidays:

     LOAD Concat(DISTINCT Holidays, '''.''') As Holidays

     Resident ....;

     Let vHolidays = '''' & Peek('Holidays') & '''';

Then this will take the holidays into account

     =NetWorkDays(ClosedDate, OpenedDate, $(vHolidays));

(all the quotes above a are single quotes, escaped by doubling)

[Anonymous]

Hi Chris,

Networkdays (start_date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays.

Networkdays accepts a third parameter (with a field that has list of holidays)

All parameters should be valid dates or timestamps.

=NetWorkDays(StartDate, CompletedDate, vHolidayList)

Regards

Neetha