Help with time formatting needed.
Publicado 16 mar 2023
Hi, our requirement is to sum new status time in minutes with open status time in minutes of tickets and to display it in Days:Hours:Minutes format. There is 1 row per ticket ID in the report.
We are currently unable to fulfill this, our calculated metric formula looks like:
IF SUM(New status time (min)) != NULL AND SUM(Open status time (min)) != NULL
THEN (SUM(New status time (min)) + SUM(Open status time (min)))*60
ELIF SUM(New status time (min)) = NULL AND SUM(Open status time (min)) != NULL
THEN SUM(Open status time (min))*60
ELIF SUM(New status time (min)) != NULL AND SUM(Open status time (min)) = NULL
THEN SUM(New status time (min))*60
ELSE 0
ENDIF
Using this formula and the built in duration time format we correctly get time spent in Hours:Minutes:Seconds format. How can we convert this to the desired format of Days:Hours:Minutes?
My attempt was the code below, but after testing smaller bits of it, turns out a calculated metric cannot be a string (because of quantifiability) and using calculated attribute broke in other ways.
IF SUM(New status time (min)) != NULL AND SUM(Open status time (min)) != NULL
THEN
FLOOR(((SUM(New status time (min)) + SUM(Open status time (min)))/60)/24)
+ ":"
+ FLOOR((SUM(New status time (min)) + SUM(Open status time (min)))/60)
+ ":"
+ (SUM(New status time (min)) + SUM(Open status time (min)))%60ELIF SUM(New status time (min)) = NULL AND SUM(Open status time (min)) != NULL
THEN
((SUM(Open status time (min))/60)/24
+ ":"
+ (SUM(Open status time (min))/60
+ ":"
+ (SUM(Open status time (min))%60ElIF SUM(New status time (min)) != NULL AND SUM(Open status time (min)) = NULL
THEN
((SUM(New status time (min))/60)/24
+ ":"
+ (SUM(New status time (min))/60
+ ":"
+ (SUM(New status time (min))%60ELSE 0
ENDIF
We will greatly appreciate any help.
0
1 comentario
Viktor Osetrov
Could you please use the following Standard Calculated Metric formula without `*60`:
I have tested it works for me as expected:
Please notice it works only for the following dataset: Support Updates history [default]
Hope it helps
0
Iniciar sesión para dejar un comentario.