Help with time formatting needed.

Respondidas


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)))%60

ELIF 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))%60

ElIF 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))%60

ELSE 0
ENDIF

We will greatly appreciate any help.

 


0

1

1 comentario

Hello Krystina,
 
Could you please use the following Standard Calculated Metric formula without `*60`:
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)))
ELIF SUM(New status time (min)) = NULL AND SUM(Open status time (min)) != NULL
THEN SUM(Open status time (min))
ELIF SUM(New status time (min)) != NULL AND SUM(Open status time (min)) = NULL
THEN SUM(New status time (min))
ELSE 0
ENDIF
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.

¿No encontró lo que buscaba?

Nueva publicación