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?
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.