Vor Kurzem aufgerufene Suchen
Keine vor kurzem aufgerufene Suchen
Creating a calculated metric to calculate the full resolution time without the lunch break
Gepostet 23. Sept. 2022
Hello,
I can't upgrade to set a different set of planing for each team and I would like to calculate the full resolution time without the lunch break (I don't want my team to be impacted because of that :) )
For this I am trying to create a calculated metric that will modify the full resolution time but there seems to be a problem with the syntax and I don't know where it could be...
Here is the formula, if anyone can help it would be very appreciated:
IF ([Ticket created - Date]=[Ticket solved - Date] AND [Ticket created - Hour]<ARRAY("12:30") AND [Ticket solved - Hour]>ARRAY("13:30")) THEN VALUE(Full resolution in business hours)-1
ELSE IF ([Ticket created - Date]=[Ticket solved - Date] AND [Ticket created - Hour]<ARRAY("13:30") AND [Ticket solved - Hour]>ARRAY("13:30") ) THEN VALUE(Full resolution in business hours)-(ARRAY("13:30")-[Ticket created - Hour])
ELSE IF ([Ticket created - Date]<[Ticket solved - Date] AND [Ticket created - Hour]<ARRAY("12:30") AND [Ticket solved - Hour]>ARRAY("12:30") ) THEN VALUE(Full resolution in business hours)-(DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days"))
ELSE IF ([Ticket created - Date]<[Ticket solved - Date] AND [Ticket created - Hour]<ARRAY("12:30") AND [Ticket solved - Hour]>ARRAY("13:30")) THEN VALUE(Full resolution in business hours)-(1+DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days"))
ELSE IF ( [Ticket created - Date]<[Ticket solved - Date] AND [Ticket created - Hour]>ARRAY("13:30") AND [Ticket solved - Hour]>ARRAY("13:30")) THEN VALUE(Full resolution in business hours)-(DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days"))
ELSE IF (DATE_ADD([Ticket created - Date],'day',1)<[Ticket solved - Date] AND [Ticket created - Hour]>ARRAY("12:30") AND [Ticket solved - Hour]>ARRAY("13:30")) THEN VALUE(Full resolution in business hours)-(1+DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days")) ELSE VALUE(Full resolution in business hours) ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF
Thank you!
0
3 Kommentare
Omar
Cant help with this one, but wondering if you would know if there is a syntax to create a metric which will account for shift pattern
Better still, when that shift patter changes weekly
e.g. agent A Mon-Fri working 5am-10am
Agent A the next week works 11am-4pm
Agent A the third week works again 5am-10am
thanks
0
CJ Johnson
I am pretty sure this is not possible due to the constraint that you cannot "date_diff" two custom timestamps. I can't see any way you could calculate this with a lunch break without using two custom timestamps to represent like, before lunch and after.
0
BO Zappier
I solved the format issue! But now I am facing another issue of computation with this error: fail to calculate AVG(Resolution in business hours) because the following values can't be evaluated {1}
Here is the code:
IF (DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days")=0) AND ([Ticket created - Hour]<ARRAY("12:30")) AND ([Ticket solved - Hour]>ARRAY("13:30")) THEN VALUE(Full resolution time - Business hours (hrs))-1
ELSE IF (DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days")=0) AND ([Ticket created - Hour]<ARRAY("13:30")) AND ([Ticket solved - Hour]>ARRAY("13:30")) THEN VALUE(Full resolution time - Business hours (hrs))-(ARRAY("13:30")-[Ticket created - Hour])
ELSE IF (DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days")>=1) AND ([Ticket created - Hour]<ARRAY("12:30")) AND ([Ticket solved - Hour]>ARRAY("12:30")) THEN VALUE(Full resolution time - Business hours (hrs))-(DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days"))
ELSE IF (DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days")>=1) AND ([Ticket created - Hour]<ARRAY("12:30")) AND ([Ticket solved - Hour]>ARRAY("13:30")) THEN VALUE(Full resolution time - Business hours (hrs))-(1+DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days"))
ELSE IF ( DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days")>=1) AND ([Ticket created - Hour]>ARRAY("13:30")) AND ([Ticket solved - Hour]>ARRAY("13:30")) THEN VALUE(Full resolution time - Business hours (hrs))-(DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days"))
ELSE IF (DATE_ADD([Ticket created - Date],"day",1)<[Ticket solved - Date]) AND ([Ticket created - Hour]>ARRAY("12:30")) AND ([Ticket solved - Hour]>ARRAY("13:30")) THEN VALUE(Full resolution time - Business hours (hrs))-(1+DATE_DIFF([Ticket created - Date],[Ticket solved - Date],"nb_of_days"))
ELSE VALUE(Full resolution time - Business hours (hrs)) ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF
0