Show KPI as mm:ss

[aheavy95]

Hi all
I am trying to show a KPI in a mm:ss (minutes :seconds) manner.

This is the formula that I am using:  ' ' &'00:' & left(round (avg(ChatFirstResponse)),2)

and I'm getting:

I want to get in this case 01:03 

defying the number formatting as duration doesn't work properly

Anyone has a suiable formula for that?

Thanks 

[BrunPierre]

Since the result is displayed as a time value, then possibly this

Time(' ' &'00:' & left(round (avg(ChatFirstResponse)),2), 'hh-mm' )

[brunobertels · Accepted solution]

hi 

in your KPI just divide the result by 86400 and select format Time : 

left(round (avg(ChatFirstResponse)),2) / 86400 

then select correct time  format : 

[aheavy95]

' ' &'00:' & time(left(round (avg(ChatFirstResponse)),2))

I get 

[Øystein_Kolsrud]

I'd say you should look into what @brunobertels recommended in his reply. Let the expression be a numeric, and use the duration formatting function for the expression.

Time is handled as a numeric value in Qlik Sense with the integer part representing days. This means that the number 1.5 represents a duration of one and a half days. It looks like the number you want to represent as time represents seconds, and one second is 1/86000 of a day (60*60*24), so simply divide you expression by that number.

So to sum it up: Don't do formatting directly in the expression, but rather turn it into a numeric representation of days. Then you can use the formatting functionality of the measure to format it into a nice string representation.

[aheavy95]

That works perfectly, Thanks a lot!