Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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'
];
Hi,
This will give me total hours between 2dates but i need only the operational hours between 2 dates
Thanks,
Poojashri
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?
Hi Vegar,
Appreciate your response.
For this case:
Breakdown:
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
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.
Hi Vegar,
I gave your approach a try, but unfortunately, it doesn’t seem to be working on my end.
Regards,
Poojashri