Duration Business hours

[#1]

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

[Anonymous]

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

[Anonymous]

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

[#4]

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

[Anonymous]

Hi,

You have also 31.10.16 -> 17 -00 7 hours, right?

Best regards,

Cosmina

[#6]

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 ?

[Anonymous · Accepted solution]

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;