最近搜索


没有最近搜索

Help with time formatting needed.

Answered


已于 2023年3月16日 发布

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 条评论

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


登录 to leave a comment.

找不到所需的内容?

新建帖子