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];
