15 Replies Latest reply: Feb 3, 2017 10:27 AM by popescu cosmina RSS

    Duration Business hours

    André Kabalkin

      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
      ))
      
        • Re: Duration Business hours
          Vineeth Pujari

          can you attach sample app / data

            • Re: Duration Business hours
              André Kabalkin

              Hi,

               

              thanks for the quick response.

              in the attachment you will find the sample without data

                • Re: Duration Business hours
                  Vineeth Pujari

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

                  Preparing examples for Upload - Reduction and Data Scrambling

                    • Re: Duration Business hours
                      André Kabalkin

                      Hi Vineeth, thank you for the helpful link!

                       

                      in the attachment is the QV.

                        • Re: Duration Business hours
                          Vineeth Pujari

                          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

                            • Re: Duration Business hours
                              André Kabalkin

                              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
                                  Vineeth Pujari

                                  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

                                    • Re: Duration Business hours
                                      André Kabalkin

                                      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
                                          popescu cosmina

                                          Hi,

                                           

                                          I think you have to take in consideration also DowntimeEnd. And the result are:

                                           

                                              

                                          IncidentIdDowntimeStartDowntimeEndResult
                                          IM113089802015-11-12 07:10:412015-11-12 07:45:0900:49:19
                                          IM113996252016-02-15 09:42:492016-02-15 14:34:4300:00:00
                                          IM115070932016-05-30 11:22:432016-05-30 13:40:2100:00:00
                                          IM115931182016-08-18 09:26:112016-08-19 11:43:5500:00:00
                                          IM116282432016-09-22 06:30:092016-10-04 11:16:07136:29:51
                                          IM116655042016-10-31 07:12:152016-11-02 06:16:3023:47:45
                                          IM116899972016-11-24 10:46:532016-11-24 14:27:0100:00:00
                                          IM117082892016-12-13 12:48:122016-12-14 08:29:4515:00:00
                                          IM117091562016-12-14 08:23:492016-12-19 13:17:5745:00:00
                                          IM117159382016-12-21 09:26:082016-12-21 11:01:3300: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 8)

                                          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
                                        popescu cosmina

                                        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
                              popescu cosmina

                              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
                                  André Kabalkin

                                  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
                                      popescu cosmina

                                      Hi,

                                       

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

                                       

                                      Best regards,

                                      Cosmina

                                        • Re: Duration Business hours
                                          André Kabalkin

                                          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
                                              popescu cosmina

                                              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;