Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Duration Business hours

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:

qlikview_example1.JPGqlikview_example2.JPG

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

))

1 Solution

Accepted Solutions
cocosmina
Contributor II

Re: Duration Business 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;

15 Replies
vinieme12
Esteemed Contributor II

Re: Duration Business hours

can you attach sample app / data

Not applicable

Re: Duration Business hours

Hi,

thanks for the quick response.

in the attachment you will find the sample without data

vinieme12
Esteemed Contributor II

Re: Duration Business hours

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

Preparing examples for Upload - Reduction and Data Scrambling

Not applicable

Re: Duration Business hours

Hi Vineeth, thank you for the helpful link!

in the attachment is the QV.

vinieme12
Esteemed Contributor II

Re: Duration Business hours

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

business_hrs.PNG

Not applicable

Re: Duration Business hours

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
Esteemed Contributor II

Re: Duration Business hours

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

business_hrs_2.PNG

cocosmina
Contributor II

Re: Duration Business hours

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

Not applicable

Re: Duration Business hours

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

Community Browser