Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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

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

1 Solution

Accepted Solutions
Highlighted

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

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

View solution in original post

14 Replies
Highlighted

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

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.

Highlighted
Contributor II
Contributor II

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

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. 
 

Highlighted

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

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

Highlighted

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

Try the change mentioned below.

Highlighted
Contributor II
Contributor II

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

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

Highlighted

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

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
))
Highlighted
Contributor II
Contributor II

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

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

Highlighted

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

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

View solution in original post

Highlighted
Contributor II
Contributor II

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

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?