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
))
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;
can you attach sample app / data
Hi,
thanks for the quick response.
in the attachment you will find the sample without data
Couldn't try evaluating formulas without some data, please refer below link
Preparing examples for Upload - Reduction and Data Scrambling
Hi Vineeth, thank you for the helpful link!
in the attachment is the QV.
Hi Andre,
Use the below expression
=(NetWorkDays(timestamp(DowntimeStart,'YYYY-MM-DD hh:mm:ss'),timestamp(DowntimeEnd,'YYYY-MM-DD hh:mm:ss'),$(vHolidays))-1)
*(Time('17:00','hh:mm') - Time('08:00','hh:mm'))
+((DowntimeEnd - floor(DowntimeEnd))- (DowntimeStart - floor(DowntimeStart)))
Hi, your formel is not correctly, because I need only time outside of service hours.
If I look on IM11593118:
Start: 18.08.2016 09:26
End: 19.08.2016 11:43 The correct duration is:
18.08.: 17->24 = 7 hours +
19.08.: 00->8 = 8 hours
Sum = 7+8 = 15 hours
with my incorrect formel I get: 8:00:00
With your formel we get: 11:17:44
DowntimeStart and DowntimeEnd of IM11399625 is during the servicetime on same day: here is correct answer: 0 answer
OK, Now we have Business hours between dates so Non Business Hrs would be
TOTAL HRS - Business Hrs
So either
=interval(Interval(timestamp(DowntimeEnd,'YYYY-MM-DD hh:mm:ss')-timestamp(DowntimeStart,'YYYY-MM-DD hh:mm:ss'))-NEW_TIME_Difference)
or, complete formula
=interval(Interval(timestamp(DowntimeEnd,'YYYY-MM-DD hh:mm:ss')-timestamp(DowntimeStart,'YYYY-MM-DD hh:mm:ss'))
-
((NetWorkDays(timestamp(DowntimeStart,'YYYY-MM-DD hh:mm:ss'),timestamp(DowntimeEnd,'YYYY-MM-DD hh:mm:ss'),$(vHolidays))-1)
*(Time('17:00','hh:mm') - Time('08:00','hh:mm'))
+((DowntimeEnd - floor(DowntimeEnd))- (DowntimeStart - floor(DowntimeStart)))))
Hi,
You can add the following to the formula:
=(NetWorkDays(timestamp(DowntimeStart,'YYYY-MM-DD hh:mm:ss'),timestamp(DowntimeEnd,'YYYY-MM-DD hh:mm:ss'),$(vHolidays))-1)
*(Time('17:00','hh:mm') - Time('08:00','hh:mm'))
+
if( DowntimeStart - Floor(DowntimeStart)<= MakeTime(8,0,0),DowntimeStart - Floor(DowntimeStart)+MakeTime(7,0,0),
if(DowntimeStart - Floor(DowntimeStart)>= MakeTime(17,0,0),MakeTime(17,0,0)-DowntimeStart + Floor(DowntimeStart)
,MakeTime(7,0,0)
)
)
+
if( DowntimeEnd - Floor(DowntimeEnd)>= MakeTime(17,0,0),DowntimeEnd-Floor(DowntimeEnd)-MakeTime(17,0,0),
if(DowntimeEnd - Floor(DowntimeEnd)<= MakeTime(8,0,0),DowntimeEnd - Floor(DowntimeEnd),
MakeTime(8,0,0)
)
)
But i think you will still have one case unsolved, when the DowntimeStart or DowntimeEnd is a holiday. If this is possible you can use Subfield to check between the variable vHolidays and not calculate the if statments.
Best regards,
Cosmina
Thanks, it seems more better !
but I the formel is not finaly, because incident IM11709156 and IM11665504 deliver incorrect answer:
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= 5:04 day-total = 13:04
TotalSum: = 35:52
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
18.12.2016 15 hours
19.12.2016 13:17:57 8 hours
Total: 75:00 hours
Your formel: 93:00 hours