Solved: Re: Duration Business hours - Qlik Community

Report Inappropriate Content · ‎2016-12-27

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 · ‎2017-02-03

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;

View solution in original post

vinieme12 · ‎2016-12-27

can you attach sample app / data

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Report Inappropriate Content · ‎2016-12-27

Hi,

thanks for the quick response.

in the attachment you will find the sample without data

vinieme12 · ‎2016-12-28

Couldn't try evaluating formulas without some data, please refer below link

Preparing examples for Upload - Reduction and Data Scrambling

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Report Inappropriate Content · ‎2016-12-29

Hi Vineeth, thank you for the helpful link!

in the attachment is the QV.

vinieme12 · ‎2016-12-29

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Report Inappropriate Content · ‎2016-12-29

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

vinieme12 · ‎2016-12-29

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2016-12-29

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

Report Inappropriate Content · ‎2016-12-29

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