Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

calculation error with Networkdays() function

Hello,

I have a problem with the networkdays () function.

This function takes into account holidays that are not within the calculation interval:

for example:

My date format is as follows:

SET DateFormat = 'DD-MM-YY';

The call of the function:

Networkdays ('16 -01-17 ', '19 -01-17') referrals 4, between 16 and 19 January there are 4 working days: the result is correct.

If now I set the {holiday} parameter by simulating a holiday on 20/01/2017 (so after the 19th)

Networkdays ('16 -01-17 ', '19 -01-17', '20 -01-17 ',' 20-01-17 ') references 3.

I have this problem every time my end date and the day before a holiday.

Have you ever encountered this problem and what solution have you applied?

Thank you for your help

1 Solution

Accepted Solutions

Re: calculation error with Networkdays() function

Look here

https://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/networkd...

The above description sort of looks confusing, but I don't think you need start and end date... this seems to be working also

=NetWorkDays(MakeDate(2017, 01, 16), MakeDate(2017, 01, 20), MakeDate(2017, 01, 20))

Giving me 4, just like this is giving me 4

=NetWorkDays(MakeDate(2017, 01, 16), MakeDate(2017, 01, 19), MakeDate(2017, 01, 20))

8 Replies

Re: calculation error with Networkdays() function

I was able to replicate the issue only if I added the same date twice in my Holiday... Why are you adding it twice? Is there a reason for adding it twice

Not applicable

Re: calculation error with Networkdays() function

I apply ( but i certainly not understant) the help of the function:

start_dateThe start date to evaluate.
end_dateThe end date to evaluate.
holiday

Holiday periods to exclude from working days. A holiday period is stated as a start date and an end date, separated by commas.

in my mind, it was mandatory to specify a start and an end date even if it is the same.

Re: calculation error with Networkdays() function

Look here

https://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/networkd...

The above description sort of looks confusing, but I don't think you need start and end date... this seems to be working also

=NetWorkDays(MakeDate(2017, 01, 16), MakeDate(2017, 01, 20), MakeDate(2017, 01, 20))

Giving me 4, just like this is giving me 4

=NetWorkDays(MakeDate(2017, 01, 16), MakeDate(2017, 01, 19), MakeDate(2017, 01, 20))

Not applicable

Re: calculation error with Networkdays() function

thank for your help

Re: calculation error with Networkdays() function

No problem at all

MVP
MVP

Re: calculation error with Networkdays() function

I can only hope that the description in the online help is wrong...

AFAIK, Networkdays() required to list optional holidays, a value per holiday date.

If this has changed, I guess it will break a lot of code.

But I guess it's just one of the wrong descriptions in the Help.

This is the old help text:

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

The new help tells instead:

holiday

Holiday periods to exclude from working days. A holiday period is stated as a start date and an end date, separated by commas.

Example: '25/12/2013', '26/12/2013'

You can specify more than one holiday period, separated by commas.

Example: '25/12/2013', '26/12/2013', '31/12/2013', '01/01/2014'

But looking at the specific values it's hard to tell if it is using interval start end dates or single holiday values.

But we can easily test it:

=NetWorkDays(MakeDate(2017, 01, 16), MakeDate(2017, 01, 19), MakeDate(2017, 01, 17), MakeDate(2017, 01, 19))

returns 2, not 1. So no period is used in holidays (edit: in QV11.20 SR12)

Not applicable

Re: calculation error with Networkdays() function

That means, if we have several period of two weeks of days off  , we need to specify in the holidays list , for each period, each days of the two weeks.. .

MVP
MVP

Re: calculation error with Networkdays() function

Right, that's how I understand the function.