Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Creator II
Creator II

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

15 Replies
vinieme12
Champion II
Champion II

can you attach sample app / data

Not applicable
Author

Hi,

thanks for the quick response.

in the attachment you will find the sample without data

vinieme12
Champion II
Champion II

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

Preparing examples for Upload - Reduction and Data Scrambling

Not applicable
Author

Hi Vineeth, thank you for the helpful link!

in the attachment is the QV.

vinieme12
Champion II
Champion II

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
Author

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
Champion II
Champion II

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
Creator II
Creator II

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
Author

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