topic Re: Operational Working Hours Calculation in Qlik in App Development

Operational Working Hours Calculation in Qlik

poojashribanger — Tue, 18 Mar 2025 12:15:12 GMT

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

Re: Operational Working Hours Calculation in Qlik

Vegar — Wed, 19 Mar 2025 05:33:09 GMT

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'

];

Re: Operational Working Hours Calculation in Qlik

poojashribanger — Wed, 19 Mar 2025 05:43:34 GMT

Hi,

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

Thanks,

Poojashri

Re: Operational Working Hours Calculation in Qlik

Vegar — Wed, 19 Mar 2025 13:04:12 GMT

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?

Re: Operational Working Hours Calculation in Qlik

poojashribanger — Thu, 20 Mar 2025 11:12:25 GMT

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

Re: Operational Working Hours Calculation in Qlik

Vegar — Thu, 20 Mar 2025 22:43:02 GMT

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

Re: Operational Working Hours Calculation in Qlik

poojashribanger — Tue, 25 Mar 2025 06:34:49 GMT

Hi Vegar,

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

Regards,
Poojashri