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.