Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Calculating SLA with ticket created date(timestamp) and first responce time(timestamp) considering only working hours from 8AM to 5Pm and excluding Weekends SAT&SUN. Also need SLA to be in ''hh.mm.ss" format.
for e.g. if created on date is 19.06.2024 10:11:00
first responce time is 21.06.2024 11:11:00
SLA will be 19:11.00
e.g.2
if created on date is 19.06.2024 10:11:00
first responce time is 24.06.2024 11:11:00
SLA will be 28:11.00 which means it took 28 hr 11min and 00 sec to respond
I have reffered Solved: Calculate hours between two Date/Time strings - Qlik Community - 380745
to be specific 7th reply. And it worked for me!!!!!
I have tried
but not working for me.
Hi
Take care to the date, the time and timestamp format
but try this
Assuming a created date aned first response date are always on a working day you can split your mesure like this
duration between end of day of the created date at 17:00 and created date
'19/06/2024 17:00:00' - '19/06/2024 10:11:00' = 06:49:00
duration between first response date and opening business :
'24/06/2024 11:11:00' - '24/06/2024 08:00:00' = 03:11:00
networkdays between created date + 1 and first response date - 1 * 09:00:00
it will give you :
interval(
dayend(Date(floor('19/06/2024 10:11:00'),'DD.MM.YYYY'))-0.291666
-
'19/06/2024 10:11:00'
)
+
interval(
'24/06/2024 11:11:00'
-
(DayStart(Date(floor('24/06/2024 10:11:00'),'DD.MM.YYYY'))+maketime(8))
)
+
interval(NetWorkDays(Date(floor('19/06/2024 10:11:00')+1,'DD.MM.YYYY'),Date(floor('24/06/2024 11:11:00'),'DD.MM.YYYY')-1)*'09:00:00')
Thank you so much @brunobertels for your response!
I have reffered Solved: Calculate hours between two Date/Time strings - Qlik Community - 380745
to be specific 7th reply. And it worked for me!!!!!