Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
poojashribanger
Creator II
Creator II

Operational Working Hours Calculation in Qlik

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

Labels (3)
6 Replies
Vegar
MVP
MVP

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

Hi,

 

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

 

Thanks,

Poojashri

Vegar
MVP
MVP

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.

Vegar_0-1742389288819.png

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

 

poojashribanger
Creator II
Creator II
Author

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

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. 

Vegar_0-1742510536670.png

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

Hi Vegar,

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

 

poojashribanger_0-1742884414588.png

 

Regards,
Poojashri