topic Re: calculation error with Networkdays() function in App Development

calculation error with Networkdays() function

Tue, 14 Mar 2017 08:52:58 GMT

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

Re: calculation error with Networkdays() function

sunny_talwar — Tue, 14 Mar 2017 09:39:14 GMT

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

Re: calculation error with Networkdays() function

Tue, 14 Mar 2017 09:46:27 GMT

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

start_date	The start date to evaluate.
end_date	The 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

sunny_talwar — Tue, 14 Mar 2017 09:55:00 GMT

Look here

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

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))

Re: calculation error with Networkdays() function

Tue, 14 Mar 2017 09:59:00 GMT

thank for your help

Re: calculation error with Networkdays() function

sunny_talwar — Tue, 14 Mar 2017 10:00:32 GMT

No problem at all

Re: calculation error with Networkdays() function

swuehl — Tue, 14 Mar 2017 10:08:20 GMT

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)

Re: calculation error with Networkdays() function

Tue, 14 Mar 2017 10:36:59 GMT

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.. .

Re: calculation error with Networkdays() function

swuehl — Tue, 14 Mar 2017 11:55:50 GMT

Right, that's how I understand the function.

Re: calculation error with Networkdays() function

ajayvermaida — Fri, 07 Feb 2020 06:10:33 GMT

Hi Rob,

can you please help me to calculating the time diff between two dates based on given points

1- First I have to calculate the time weekdays in between 7 Am to 11 PM excluding sat and sun . am using below script for this but prob is that when both the dates are weekend then its also calculate the time diff .

if(not WildMatch(IssueStatus,'Pending') , (Interval((RangeMin(frac(IssueResolutionDate), MakeTime(23))- RangeMax(frac(IssueCreateDate), MakeTime(7)))+
( NetWorkDays(IssueCreateDate, IssueResolutionDate-1) * MakeTime(16)) ))) as TotalTime,

2- Second I have to calculate the time diff only Weekend Sat or Sun in between 10 Am to 4 PM . also if any dates is weekend and other one is weekday then it calculate the time based on diff time difference .

3- if any ticket open in the last month and closed in next month then it calculate the time as point 1 except if it comes on weekdays .

Please help me to find the correct result

Thanks

Ajay

Re: calculation error with Networkdays() function

ajayvermaida — Fri, 07 Feb 2020 06:09:59 GMT

Hi Sunny,

can you please help me to calculating the time diff between two dates based on given points

3- if any ticket open in the last month and closed in next month then it calculate the time as point 1 except if it comes on weekdays .

Please help me to find the correct result

Thanks

Ajay