Operational Working Hours Calculation in Qlik

[poojashribanger]

Hi All,

I have a scenario where i need to calculate the operational hours between ticket created and resolved but considering only the operational hours.

i.e

.operation start= 10:00 Am
operation end =2:00 am next day mid night

For some case the values are not calculating correctly

Let vStartTime = Num(MakeTime(10, 0, 0));
Let vEndTime = Num(MakeTime(2, 0, 0)) +1; // +1 for midnight the next day / 24:00
// Let vEndTime = Floor(Num(MakeTime(0, 0, 0))) + 1 + (6 / 24);

Data:
NoConcatenate Load
TicketId,
Reported,
Resolved,
Daysqlik,
// rangesum(if(networkdays(Reported,Resolved),
// round(rangesum(if(frac(Reported)>maketime($(vstarthours)),maketime($(vstarthours))-frac(Reported),0),
// if(frac(Resolved)>maketime($(Vendhours)),frac(Resolved)-maketime($(Vendhours)),0)) as time;
interval(RangeSum(Days,TimeAfterReported,TimeToResolved), 'hh:mm:ss') as Code_C;

Load
*,
If(ReportedTime >= $(vEndTime), 0, $(vEndTime) - RangeMax(ReportedTime, $(vStartTime))) as TimeAfterReported,
If(ResolvedTime <= $(vStartTime), 0, RangeMin(ResolvedTime, $(vEndTime)) - $(vStartTime)) as TimeToResolved;

Load
*,
(floor(Resolved) - ceil(Reported)) * ($(vEndTime) - $(vStartTime)) as Days,
interval(Resolved-Reported, 'D') as Daysqlik,
num(Reported) - Floor(Reported) as ReportedTime,
num(Resolved) - Floor(Resolved) as ResolvedTime;

Load
RecNo() as TicketId,
Timestamp#(Reported, 'DD/MM/YYYY hh:mm:ss') as Reported,
Timestamp#(Resolved, 'DD/MM/YYYY hh:mm:ss') as Resolved;

load * Inline [
Reported, Resolved
'17/06/2024 11:10:00', '18/06/2024 2:00:00'
'13/05/2024 8:00:00', '15/05/2024 4:00:00'
'26/02/2024 10:25:00', '27/02/2024 1:05:00'
];

exit script;

Expected Output :

I have attached the file.

Kindly help me on this

Thanks & Regards,
Poojashri

[Vegar]

I think you could do the calculations by using the timestamps directly as shown below.

Load 

Interval(Resolved-Reported) as TimeToResolved ;

Load

RecNo() as TicketId,

Timestamp#(Reported, 'DD/MM/YYYY hh:mm:ss') as Reported,

Timestamp#(Resolved, 'DD/MM/YYYY hh:mm:ss') as Resolved;

load * Inline [

Reported, Resolved

'17/06/2024 11:10:00', '18/06/2024 2:00:00'

'13/05/2024 8:00:00', '15/05/2024 4:00:00'

'26/02/2024 10:25:00', '27/02/2024 1:05:00'

];

[poojashribanger]

Hi,

This will give me total hours between  2dates but i need only the operational hours between 2 dates

Thanks,

Poojashri

[Vegar]

I am not sure if I understand. There is some detail I am missing in your explanation. 

I had a look at your attached image. I get as you did, the correct value with my suggested solution on 2 out of 3, but we differ in which ones that are wrong. See image below.

Can you explain why TicketId 2 should have a Time to Resolved to 32h and not 44h as I get in my calculation?

[poojashribanger]

Hi Vegar,

Appreciate your response.

For this case:

• Operational hours are from 10:00 AM to 2:00 AM (next day).
• The ticket was created on 13/05/2024 at 8:00 AM, but operations start at 10:00 AM.
• The ticket was closed on 15/05/2024 at 4:00 AM, but since operations end at 2:00 AM, we only consider up to that time.

Breakdown:

• 13th May: Operational hours = 10:00 AM to 2:00 AM (16 hours)
• 14th May: Operational hours = 10:00 AM to 2:00 AM (16 hours)

Even though the ticket was closed at 4:00 AM on the 15th, operations had already stopped at 2:00 AM, so only the first two days' operational hours (16 + 16 = 32 hours) should be considered.

Let me know if you need further clarification.

Thanks,
Poojashri

[Vegar]

Interesting problem. I did some thinking and came up with this solution.Hopefully it will solve your need. At least it gets your examples correct. 

tickets:

Load

  RecNo() as TicketId,

  Timestamp#(Reported, 'DD/MM/YYYY hh:mm:ss') as Reported,

  Timestamp#(Resolved, 'DD/MM/YYYY hh:mm:ss') as Resolved,

  interval#(ExpectedTimeToResolved,'hh:mm') as [ExpectedTimeToResolved (hh:mm)]

;

load * 

Inline [

  Reported, Resolved, ExpectedTimeToResolved,

  '17/06/2024 11:10:00', '18/06/2024 2:00:00', 14:50

  '13/05/2024 8:00:00', '15/05/2024 4:00:00',32:00

  '26/02/2024 10:25:00', '27/02/2024 1:05:00',14:40

  ];

let start = '26/02/2024';  //The Min Reported date

let end = '18/06/2024';    //The Max Resolved date

datehours:

LOAD 

   timestamp( '$(start)'+IterNo()-1 + maketime(recno()-1), 'DD/MM/YYYY hh:mm')  as date_hour

AutoGenerate 24

while 

   '$(start)'+IterNo()-1<= '$(end)'

   and match((recno()-1), 10,11,12,13,14,15,16,17,18,19,20,21,22,23,0,1) //list the operation operating hours

;

IntervalMatch (date_hour)

left JOIN (tickets) LOAD Reported, Resolved Resident tickets;

drop table datehours;

load 

    TicketId,

    only(Reported) as Reported,

    only(Resolved) as Resolved,

    interval(

      (

      interval#(count(TicketId), 'h') 

      +interval#( mod(60-minute(only(Reported)),60), 'm')

      -interval#( mod(60-minute(only(Resolved)),60), 'm')

      ),

      'hh:mm') as [Operational Working Hours]

Resident 

tickets

Group by TicketId;

drop table tickets

[poojashribanger]

Hi Vegar,

I gave your approach a try, but unfortunately, it doesn’t seem to be working on my end.

Regards,
Poojashri