topic Re: NetWorkDays function in QlikView

NetWorkDays function

Mon, 26 Jan 2015 13:31:57 GMT

Hello everyone,

For example, I have 2 dates : DateA = 18/12/2014 and DateB = 13/01/2015 (in format dd/mm/yyyy)

If I do DateB-DateA, I get 26 which is ok. (31-18) + 13 = 26

If I do NetWorkDays(DateA,DateB), I expect to obtain 18 because between Thursday 18th and Friday 13rd, there are 4 weekends so 8 weekend days, so I should obtain 26-8 = 18.

But I get 19.

Does NetworkDays include DateA and DateB in the count and so is not equal to : DateB-DateA - number of Sundays &Saturdays ?

Re: NetWorkDays function

alexandros17 — Mon, 26 Jan 2015 13:36:40 GMT

yes the 2 dates are included

Re: NetWorkDays function

Colin-Albert — Mon, 26 Jan 2015 13:42:13 GMT

The start and end days are both included in the networkdays calculation.

This is from the F1 help on networkdays.

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.

Re: NetWorkDays function

Gabriel — Mon, 26 Jan 2015 13:57:22 GMT

Hi,

As mentioned above your answer is correct.

NetWorkdays includes the start and end date in calculation of NetWorkDays.

Another example is

=NetWorkDays ('2015-01-19', '2015-01-23')

this should and will return 5 days, hence your

=NetWorkDays ('2014-12-18 ', '2015-01-13') should return 19 for you.

Re: NetWorkDays function

Mon, 26 Jan 2015 14:05:54 GMT

Thank you for your help :

I have another question:

Thanks again

Re: NetWorkDays function

alexandros17 — Mon, 26 Jan 2015 14:12:39 GMT

I think it is not possible, at least with the function NetWorkDays.

You could try to use this function to avoid saturday and sunday and then create

another function to remove your holidays

Re: NetWorkDays function

Mon, 26 Jan 2015 14:21:21 GMT

Ok, that's a pity.

Thank you for your answer

Re: NetWorkDays function

alexandros17 — Mon, 26 Jan 2015 14:27:22 GMT

Could you please mark the answers ... if you want ....

Thanks

Re: NetWorkDays function

Gabriel — Mon, 26 Jan 2015 14:44:17 GMT

Hi,

I think you can have the list of Holidays stored in a variable and just use the variable in the NetWorkday function as this

SET vHolidays = "'20-Jan-2015','03-01-2015','09-01-2015'";

At the start & end of the date for holiday use double quote and single quote

Then in your NetWorkDays function you have

=NetWorkdays('01-01-2015','20-01-2015',$(vHolidays))

Give that a try

Re: NetWorkDays function

Mon, 26 Jan 2015 14:56:58 GMT

Thank you Gabriel.

I know I can do what you suggest. What I would like to do is to specify a date without the year, eg. "25/12" for Christmas without having to write it for each year.

Re: NetWorkDays function

Gabriel — Mon, 26 Jan 2015 16:03:23 GMT

Hi,

I think for simplicity it's best to include Year or you'll get to a stage of nesting variables to accommodate for current year.

Re: NetWorkDays function

Anonymous — Mon, 26 Jan 2015 16:13:31 GMT

See this:

http://community.qlik.com/docs/DOC-5741