Time Difference in Business Hours

[KD_]

Hello Qlik world. I am new to Qlik & I have a requirement to calculate the time length between the time a Ticket is opened and the time it is closed (...the fields are Ticket_Opened_Date & Ticket_Closed_Date). One metric should be the number of business days it takes a ticket to be opened and closed. I am familiar with the Networkdays() function so I am able to tackle this requirement.

However, the business also want to see the time difference in business Hours, with the working day starting at 8:00am & ending at 4:00pm. So we will say if a Ticket came in on a business day at 12:00 Noon & is closed at 3:00pm on the same day, then the calculation returns 3 Hours. And if another Ticket is opened at the same time, 12:00 Noon but is closed at 10AM on the next day which is also a business day, the output is 6 Hours (rounding up & down by minutes). I am aware there are already many posts on this topic & I have read a few but I can't seem to find one that addresses this exact scenario. Thank you in advance.

[Gabbar]

Use networkdays() to calculate number of days, lets call working Days as WD, 
Now as you have 8 hours a day, multiply WD by 8 to get Temp Working Hours as TWD.

Now your work days starts at 8, So Subtract This 8 from both Ticket Came time TCT and Ticket Ending Time  TET.
TCT and TET are just Hours for same date structure.
Now your calculation is TWD - TCT + TET.

Your example, Ticket Opened at 12 Noon and closed 10 Am on next Day.
TWD will be 8, TCT will be 4 and TET will be 2.
So Overall 8-4+2 = 6.
Now for same day ticket open at 12 and Close at 3 Pm.
TWD will be 0, TCT will be 4 and TET will be 7, Result in overall time taken as 3.

[Aditya_Chitale]

@KD_ 

Try this expression in your table. It's a bit lengthy but I have added comments. You can break it into parts and evaluate as separate dimensions/measures to understand better.

Note: I have considered 14th & 27th Nov 2023 as Holidays

=// Condition to check if Ticket Opened Day is working day or not
if(
FirstWorkDate(
Date(floor(ticketEnd),'MM/DD/YYYY') , NetWorkDays(ticketStart,ticketEnd,'11/14/2023','11/27/2023')
, '11/14/2023','11/27/2023')
=
Date(floor(ticketStart),'MM/DD/YYYY')
,

// If it is working day, calculated working hours from ticket opended time on that day
((NetWorkDays(ticketStart,ticketEnd,'11/14/2023','11/27/2023') * 8 // 8 is total working hours on a working day
-
(16 - hour(timestamp(timestamp#(ticketEnd, 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm:ss'))))
-
(if(hour(timestamp(timestamp#(ticketStart, 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm:ss'))=0,12,hour(timestamp(timestamp#(ticketStart, 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm:ss'))) - 8

,

// if there is holiday on & after ticket start day, subtracted ticket end time from Office End time
(NetWorkDays(ticketStart,ticketEnd,'11/14/2023','11/27/2023') * 8
-
(16 - hour(timestamp(timestamp#(ticketEnd, 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm:ss')))
)

Output:

There might be a better & simpler solution. Let me know if you found the same.

Regards,

Aditya