Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlikers,
I have a requirement to calculate the time of a request outside the service times:
Below are the considerations
1) Count only outside of working hours ( 8 AM - 5 PM --> 15 hours/per day)
2) Exclude Saturdays and Sundays
Based on the post https://community.qlik.com/docs/DOC-16489 I follow the logic:
working hours from 0 to 24 with "lunch time" 8am to 5pm.
But I don't get the correct time calculation from the first day and if difference is only one second:
Here is code:
rangesum( rangesum(NetWorkDays(DowntimeStart+1,DowntimeEnd-1) * MakeTime(15), // 15 ! hours per workday, for all day inbetween the period, excluding bounderies
if(NetWorkDays(DowntimeEnd,DowntimeEnd) ,
rangesum(rangemin(frac(DowntimeEnd),MakeTime(8))- rangemin(frac(DowntimeEnd),MakeTime(0)) ,
rangemin(frac(DowntimeEnd),MakeTime(24)) - rangemin(frac(DowntimeEnd),MakeTime(17))),0), // working hours last day
if(NetWorkDays(DowntimeStart,DowntimeStart),
rangesum(MakeTime(8) - rangemin(rangemax(frac(DowntimeStart), MakeTime(0)),MakeTime(8)),
MakeTime(24) - rangemax(rangemin(frac(DowntimeStart), MakeTime(24)),MakeTime(17))),0), // working first day
if(NetWorkDays(DowntimeStart,DowntimeStart) and floor(DowntimeStart)=floor(DowntimeEnd),-MakeTime(15)) // If the same day then correct the hours
))
Hi,
I think you have to take in consideration also DowntimeEnd. And the result are:
IncidentId | DowntimeStart | DowntimeEnd | Result |
IM11308980 | 2015-11-12 07:10:41 | 2015-11-12 07:45:09 | 00:49:19 |
IM11399625 | 2016-02-15 09:42:49 | 2016-02-15 14:34:43 | 00:00:00 |
IM11507093 | 2016-05-30 11:22:43 | 2016-05-30 13:40:21 | 00:00:00 |
IM11593118 | 2016-08-18 09:26:11 | 2016-08-19 11:43:55 | 00:00:00 |
IM11628243 | 2016-09-22 06:30:09 | 2016-10-04 11:16:07 | 136:29:51 |
IM11665504 | 2016-10-31 07:12:15 | 2016-11-02 06:16:30 | 23:47:45 |
IM11689997 | 2016-11-24 10:46:53 | 2016-11-24 14:27:01 | 00:00:00 |
IM11708289 | 2016-12-13 12:48:12 | 2016-12-14 08:29:45 | 15:00:00 |
IM11709156 | 2016-12-14 08:23:49 | 2016-12-19 13:17:57 | 45:00:00 |
IM11715938 | 2016-12-21 09:26:08 | 2016-12-21 11:01:33 | 00:00:00 |
IM11665504:
31.10.2016 07:12 --> to 8 = 48min + 17->24 =7hours ---> day-total = 07:48
01.11.2016 00->8 + 17-24=7 hours = day-total = 15
02.11.2016 00->8 + 17-->22:04 (is 06:16:30 - no case here)= 5:04 day-total = 13:04 (correct 😎
TotalSum: = 35:52 (correct 23:47:45)
Your formel: 30:00:00 hours
IM11709156:
14.12.2016 08:23:49 --> 17-24 = 7hours
15.12.2016 15 hours
16.12.2016 15 hours
17.12.2016 15 hours non-working day
18.12.2016 15 hours non-working day
19.12.2016 13:17:57 8 hours
Total: 75:00 hours (correct 45)
Your formel: 93:00 hours
And for IM11628243 we have 2016 - 09 - 22 06:30:09 - > 6:30:09-8 +7 -> 8:29:51
23- 15
24, 25 non working days - 0
26,27,28,29,30- 15*5
31,1 0
2,3 15*2
4 ->8
Total = 136:29:51
Is not this correct?
Best regards,
Cosmina
Hi,
I've made an app that take in consideration EndData Time and StartDate Time, because is better to do it in the script the formula. For optimization reason you should keep the time separately in your data model than in the same field.
I hope this helps.
Best regards,
Cosmina
Hi,
thank you for your ivestigate. I have to count all time outside of business hours (08:00-17:00).
If I look on IM11665504:
31.10 .16 at 07:12:15 to -->08:00:00 = 00:47:15
01.11.16 (24-(17-8)) =15:00:00
02.11.16 00:00-> = 06:16:30
correct Total =22:04:15
Your result test: =29:04:45
Hi,
You have also 31.10.16 -> 17 -00 7 hours, right?
Best regards,
Cosmina
Hi, sorry for the long response time. Yes, you are right. Unfortunately I cannot open your "Business Hours Scripting.qvw". Can you please send me your formula, So I can compare the time with the other incidents ?
DATA:
LOAD * Inline [
IncidentId ,DowntimeStart ,DowntimeEnd ,Result
IM11308980 ,2015-11-12 07:10:41 ,2015-11-12 07:45:09 ,00:34:28
IM11399625 ,2016-02-15 09:42:49 ,2016-02-15 14:34:43 ,00:00:00
IM11507093 ,2016-05-30 11:22:43 ,2016-05-30 13:40:21 ,00:00:00
IM11593118 ,2016-08-18 09:26:11 ,2016-08-19 11:43:55 ,15:00:00
IM11628243 ,2016-09-22 06:30:09 ,2016-10-04 11:16:07 ,121:29:51
IM11665504 ,2016-10-31 07:12:15 ,2016-11-02 06:16:30 ,29:04:15
IM11689997 ,2016-11-24 10:46:53 ,2016-11-24 14:27:01 ,00:00:00
IM11708289 ,2016-12-13 12:48:12 ,2016-12-14 08:29:45 ,15:00:00
IM11709156 ,2016-12-14 08:23:49 ,2016-12-19 13:17:57 ,45:00:00
IM11715938 ,2016-12-21 09:26:08 ,2016-12-21 11:01:33 ,00:00:00];
NoConcatenate
Transformation:
LOAD IncidentId,
timestamp#(DowntimeStart,'YYYY-MM-DD hh:mm:ss') AS DowntimeStart,
timestamp#(DowntimeEnd,'YYYY-MM-DD hh:mm:ss') AS DowntimeEnd,
Result
Resident DATA;
DROP TABLE DATA;
SET vHolidays='2016-12-25','2016-12-26';
Split_Data:
LOAD *,
date(Floor(DowntimeStart),'YYYY-MM-DD') as DataStart,
time(DowntimeStart-Floor(DowntimeStart),'hh:mm:ss') as TimeStart,
date(Floor(DowntimeEnd),'YYYY-MM-DD') as DataEnd,
time(DowntimeEnd- Floor(DowntimeEnd),'hh:mm:ss') as TimeEnd
Resident Transformation;
drop table Transformation;
Data:
LOAD *,
time(if(DataStart<>DataEnd,if(TimeStart<=MakeTime(8),maketime(8)-TimeStart+MakeTime(7)
,if(TimeStart>=MakeTime(17),makeTime(24)-TimeStart,MakeTime(7))))
,'hh:mm:ss') as Dif1,
time(if(DataStart<>DataEnd,if(TimeEnd<=MakeTime(8),TimeEnd
,if(TimeEnd<=MakeTime(17),MakeTime(8),TimeEnd-MakeTime(17))))
,'hh:mm:ss') as Dif2,
time(if(DataStart=DataEnd,if(TimeEnd<=MakeTime(8) or TimeEnd>=MakeTime(17),TimeEnd-TimeStart
,if(TimeStart<MakeTime(8) and TimeEnd<MakeTime(17),MakeTime(8)-TimeStart
,if(TimeStart<MakeTime(8) and TimeEnd>=MakeTime(17),MakeTime(8)-TimeStart+TimeEnd-MakeTime(17)
,if(TimeStart>MakeTime(8) and TimeStart<MakeTime(17) and TimeEnd>=MakeTime(17),TimeEnd-MakeTime(17)
))))) ,'hh:mm:ss') as Dif3,
num(if(DataStart<>DataEnd,NetWorkDays(DataStart,DataEnd,$(vHolidays))-2) *0.625) as Dif4
Resident Split_Data;
drop table Split_Data;
Data_Final:
LOAD *,
Interval( if(isnull(Dif1),0,Dif1)+if(isnull(Dif2),0,Dif2)+if(isnull(Dif3),0, Dif3)+if(isnull(Dif4),0,Dif4),'hh:mm:ss') as ResultTest
Resident Data;
drop table Data;