topic Re: Duration Business hours in QlikView

Duration Business hours

Tue, 27 Dec 2016 13:01:13 GMT

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

Re: Duration Business hours

vinieme12 — Tue, 27 Dec 2016 13:08:35 GMT

can you attach sample app / data

Re: Duration Business hours

Tue, 27 Dec 2016 14:11:15 GMT

Hi,

thanks for the quick response.

in the attachment you will find the sample without data

Re: Duration Business hours

vinieme12 — Wed, 28 Dec 2016 15:50:04 GMT

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

Preparing examples for Upload - Reduction and Data Scrambling

Re: Duration Business hours

Thu, 29 Dec 2016 08:27:10 GMT

Hi Vineeth, thank you for the helpful link!

in the attachment is the QV.

Re: Duration Business hours

vinieme12 — Thu, 29 Dec 2016 09:59:36 GMT

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

Re: Duration Business hours

Thu, 29 Dec 2016 13:02:36 GMT

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

Re: Duration Business hours

vinieme12 — Thu, 29 Dec 2016 13:40:37 GMT

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

Re: Duration Business hours

Anonymous — Thu, 29 Dec 2016 13:57:15 GMT

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

Re: Duration Business hours

Thu, 29 Dec 2016 14:10:00 GMT

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

Re: Duration Business hours

Anonymous — Thu, 29 Dec 2016 15:56:28 GMT

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

Re: Duration Business hours

Anonymous — Fri, 30 Dec 2016 07:26:23 GMT

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

Re: Duration Business hours

Fri, 30 Dec 2016 12:28:58 GMT

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

Re: Duration Business hours

Anonymous — Tue, 03 Jan 2017 14:34:49 GMT

Hi,

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

Best regards,

Cosmina

Re: Duration Business hours

Fri, 03 Feb 2017 15:06:32 GMT

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 ?

Re: Duration Business hours

Anonymous — Fri, 03 Feb 2017 15:27:20 GMT

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;