Networkdays does not include holidays although correct syntax - Which settings affect the function?

[derChristopher]

Dear all,

I try to use the networkdays() function in a load script, to evaluate the runtime of tickets (services requests/incidents) according to the working/business hours of my company. Therefore I came up with a solution based on other community posts to calculate the runtime based on a 10hours day excluding weekends.  The final step is now to exclude the holidays as well. Consequently I created a variable with the holidays in regular date format (e.g. 43586 -> 01/05/2019 DD/MM/YYYY). 

So far, so good. Everything should work and works in a example application but not in production application. I think there is some variable or setting which is in conflict with networkdays(). Is that possible? 

Please take a look at my code snippet. As result RuntimeVar returns a runtime of 52:41:38 which is correct. The production application returns 62:41:38 instead, because it neglected the first of may holiday. I have no idea why this happens. The code is the same in both apps. I appreciate any help/suggestions! 

Kind regards,
Christopher

LET vStartHour = 7;
LET vEndHour = 17;
LET vWorkingHourPerDay = $(vEndHour) -$(vStartHour);

SET calc_laufzeit = 'Interval(rangesum(
NetWorkDays($1+1,$2-1,$(vHolidays)) * MakeTime(10) // 10 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays($2,$2,$(vHolidays)),Rangemin(rangemax(frac($2),maketime(7)),maketime(17))-Rangemax(rangemin(frac($2),maketime(7)),maketime(7)),0) // working hours last day
,if(NetWorkDays($1,$1,$(vHolidays)),Rangemin(rangemax(frac($1),maketime(17)),maketime(17))-Rangemax(rangemin(frac($1),maketime(17)),maketime(7)),0) // working first day
,if(NetWorkDays($1,$1,$(vHolidays)) and floor($1)=floor($2),-MakeTime(10)) // correct for first equals last day
))';

 Holidays:
LOAD * INLINE [
Holiday
43586
43615
43626
43825
43741
];

Let counter=0;
FOR Each a in FieldValueList('Holiday') //FEIERTAGE.FEIERTAG
Let vHolidays=if($(counter)=0,chr(39) & '$(a)' & chr(39),'$(vHolidays)' & ',' & chr(39) & '$(a)' & chr(39));
Let counter = 1;
NEXT a;

Data:
LOAD *,
if(1,$(calc_laufzeit(START_TIME,END_TIME)),null()) as RuntimeVar;
LOAD *,
timestamp(timestamp#(StartTime,'MM/DD/YYYY hh:mm:ss TT')) as START_TIME,
timestamp(timestamp#(EndTime,'MM/DD/YYYY hh:mm:ss TT')) as END_TIME
Inline [
TicketNo,StartTime, EndTime
1, 4/29/2019 2:12:43 PM, 5/7/2019 4:54:21 PM];

[sunny_talwar]

When I ran this, I also got 62:41:38... I just modified the code a little

LET vStartHour = 7;
LET vEndHour = 17;
LET vWorkingHourPerDay = $(vEndHour) - $(vStartHour);

SET calc_laufzeit = 'Interval(rangesum(
NetWorkDays($1+1,$2-1,$(vHolidays)) * MakeTime(10) // 10 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays($2,$2,$(vHolidays)),Rangemin(rangemax(frac($2),maketime(7)),maketime(17))-Rangemax(rangemin(frac($2),maketime(7)),maketime(7)),0) // working hours last day
,if(NetWorkDays($1,$1,$(vHolidays)),Rangemin(rangemax(frac($1),maketime(17)),maketime(17))-Rangemax(rangemin(frac($1),maketime(17)),maketime(7)),0) // working first day
,if(NetWorkDays($1,$1,$(vHolidays)) and floor($1)=floor($2),-MakeTime(10)) // correct for first equals last day
))';

Holidays:
LOAD Concat(Chr(39) & Holiday & Chr(39), ',') as Holiday;
LOAD * INLINE [
    Holiday
    43586
    43615
    43626
    43825
    43741
];

LET vHolidays = Peek('Holiday');

Data:
LOAD *,
	 if(1,$(calc_laufzeit(START_TIME,END_TIME)),null()) as RuntimeVar;
LOAD *,
	 timestamp(timestamp#(StartTime,'MM/DD/YYYY hh:mm:ss TT')) as START_TIME,
	 timestamp(timestamp#(EndTime,'MM/DD/YYYY hh:mm:ss TT')) as END_TIME
INLINE [
    TicketNo, StartTime, EndTime
    1, 4/29/2019 2:12:43 PM, 5/7/2019 4:54:21 PM
];

Checking to see why this is different than your expectation.

[derChristopher]

Hi,

thank you for your reply! I ran your modified code and got 52:41:38 in my example application again. 52:41:38 is my expectation but in my production app, it still displays 62:41:38. 
 

[sunny_talwar]

Try this

LET vStartHour = 7;
LET vEndHour = 17;
LET vWorkingHourPerDay = $(vEndHour) - $(vStartHour);

Holidays:
LOAD Concat(Chr(39) & Holiday & Chr(39), ',') as Holiday;
LOAD * INLINE [
    Holiday
    43586
    43615
    43626
    43825
    43741
];

LET vHolidays = Peek('Holiday');

TRACE $(vHolidays);

SET calc_laufzeit = 'Interval(rangesum(
NetWorkDays($1+1,$2-1,$(vHolidays)) * MakeTime(10) // 10 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays($2,$2,$(vHolidays)),Rangemin(rangemax(frac($2),maketime(7)),maketime(17))-Rangemax(rangemin(frac($2),maketime(7)),maketime(7)),0) // working hours last day
,if(NetWorkDays($1,$1,$(vHolidays)),Rangemin(rangemax(frac($1),maketime(17)),maketime(17))-Rangemax(rangemin(frac($1),maketime(17)),maketime(7)),0) // working first day
,if(NetWorkDays($1,$1,$(vHolidays)) and floor($1)=floor($2),-MakeTime(10)) // correct for first equals last day
))';

Data:
LOAD *,
	 if(1,$(calc_laufzeit(START_TIME,END_TIME)),null()) as RuntimeVar;
LOAD *,
	 timestamp(timestamp#(StartTime,'M/D/YYYY hh:mm:ss TT')) as START_TIME,
	 timestamp(timestamp#(EndTime,'M/D/YYYY hh:mm:ss TT')) as END_TIME
INLINE [
    TicketNo, StartTime, EndTime
    1, 4/29/2019 2:12:43 PM, 5/7/2019 4:54:21 PM
];

Move SET calc_laufzeit after you create vHolidays and see if this works for you

[sunny_talwar]

Try the change mentioned below.

[derChristopher]

I have already tried that in both, the example and the production application. 

The result of the example application is correct and the production application still displays the incorrect runtime(62h).

[sunny_talwar]

Can you check what you get when you use this in a text box object of your production app

=Interval(rangesum(
NetWorkDays('4/29/2019 2:12:43 PM'+1,'5/7/2019 4:54:21 PM'-1,$(vHolidays)) * MakeTime(10) // 10 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays('5/7/2019 4:54:21 PM','5/7/2019 4:54:21 PM',$(vHolidays)),Rangemin(rangemax(frac('5/7/2019 4:54:21 PM'),maketime(7)),maketime(17))-Rangemax(rangemin(frac('5/7/2019 4:54:21 PM'),maketime(7)),maketime(7)),0) // working hours last day
,if(NetWorkDays('4/29/2019 2:12:43 PM','4/29/2019 2:12:43 PM',$(vHolidays)),Rangemin(rangemax(frac('4/29/2019 2:12:43 PM'),maketime(17)),maketime(17))-Rangemax(rangemin(frac('4/29/2019 2:12:43 PM'),maketime(17)),maketime(7)),0) // working first day
,if(NetWorkDays('4/29/2019 2:12:43 PM','4/29/2019 2:12:43 PM',$(vHolidays)) and floor('4/29/2019 2:12:43 PM')=floor('5/7/2019 4:54:21 PM'),-MakeTime(10)) // correct for first equals last day
))

[derChristopher]

Thank you for your reply! Exactly your code snippet returns in a 00:00:00 interval because of the format of the timestamps. But if I modify the timestamps from a String to timestamp#('4/29/2019 2:12:43 PM','M/D/YYYY hh:mm:ss TT'), it displays the correct time of 52h. Therefore I think there has to an issue with the input dates. I will check that.

Modified Example Code:
=Interval(rangesum(
NetWorkDays(timestamp#('4/29/2019 2:12:43 PM','M/D/YYYY hh:mm:ss TT')+1,timestamp#('5/7/2019 4:54:21 PM','M/D/YYYY hh:mm:ss TT')-1,$(vHol)) * MakeTime(10) // 10 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays(timestamp#('5/7/2019 4:54:21 PM','M/D/YYYY hh:mm:ss TT'),timestamp#('5/7/2019 4:54:21 PM','M/D/YYYY hh:mm:ss TT'),$(vHol)),Rangemin(rangemax(frac(timestamp#('5/7/2019 4:54:21 PM','M/D/YYYY hh:mm:ss TT')),maketime(7)),maketime(17))-Rangemax(rangemin(frac(timestamp#('5/7/2019 4:54:21 PM','M/D/YYYY hh:mm:ss TT')),maketime(7)),maketime(7)),0) // working hours last day
,if(NetWorkDays(timestamp#('4/29/2019 2:12:43 PM','M/D/YYYY hh:mm:ss TT'),timestamp#('4/29/2019 2:12:43 PM','M/D/YYYY hh:mm:ss TT'),$(vHol)),Rangemin(rangemax(frac(timestamp#('4/29/2019 2:12:43 PM','M/D/YYYY hh:mm:ss TT')),maketime(17)),maketime(17))-Rangemax(rangemin(frac(timestamp#('4/29/2019 2:12:43 PM','M/D/YYYY hh:mm:ss TT')),maketime(17)),maketime(7)),0) // working first day
,if(NetWorkDays(timestamp#('4/29/2019 2:12:43 PM','M/D/YYYY hh:mm:ss TT'),timestamp#('4/29/2019 2:12:43 PM','M/D/YYYY hh:mm:ss TT'),$(vHol)) and floor(timestamp#('4/29/2019 2:12:43 PM','M/D/YYYY hh:mm:ss TT'))=floor(timestamp#('5/7/2019 4:54:21 PM','M/D/YYYY hh:mm:ss TT')),-MakeTime(10)) // correct for first equals last day
))

[sunny_talwar · Accepted solution]

Is there a difference in environmental variable between sample and prod app? Do they both have this?

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

[derChristopher]

No, the production app keeps the timestamps in TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]', because timestamps of the input dates match exactly to this. Therefore it shouldn't be a problem or not?