topic Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi in QlikView

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

derChristopher — Sat, 16 Nov 2024 20:48:46 GMT

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

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

sunny_talwar — Thu, 23 May 2019 12:46:22 GMT

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.

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

derChristopher — Thu, 23 May 2019 12:59:59 GMT

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.

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

sunny_talwar — Thu, 23 May 2019 13:01:43 GMT

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

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

sunny_talwar — Thu, 23 May 2019 13:02:26 GMT

Try the change mentioned below.

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

derChristopher — Thu, 23 May 2019 13:08:40 GMT

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

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

sunny_talwar — Thu, 23 May 2019 13:11:44 GMT

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

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

derChristopher — Thu, 23 May 2019 13:22:40 GMT

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

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

sunny_talwar — Thu, 23 May 2019 13:25:10 GMT

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

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

derChristopher — Thu, 23 May 2019 13:34:51 GMT

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?

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

derChristopher — Thu, 23 May 2019 13:53:25 GMT

I double checked it and found my mistake. The incorrect result was produced due to a wrong timestampformat in a preceeding step. Thank you for troubleshooting! I appreciate it 🙂

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

sunny_talwar — Thu, 23 May 2019 13:53:49 GMT

So dev has one format and production has another? Can you change the one in dev to see if that causes the result to change and become incorrect?

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

derChristopher — Thu, 23 May 2019 14:03:10 GMT

"Dev" was just the application example I made up for the community. Everything is fine. Thank you very much!

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

sunny_talwar — Thu, 23 May 2019 14:13:20 GMT

Yes that is what I meant... so everything works now? What did you end up doing to resolve it?

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

derChristopher — Fri, 24 May 2019 05:53:20 GMT

Yes everything works now. As I said before. The issue was resolved by the correct formatting of the input timestamp in a preceeding step.

Re: Networkdays does not include holidays although correct syntax - Which settings affect the functi

hadari — Thu, 20 Jun 2024 22:29:43 GMT

Hi! If I want my endhour to be 20:30 pm,how do I write this variable? It calculate correctly of I write 20 as 20:00 pm but for 20:30 I wrote 20.5 and it doesnt work well.