topic Displaying 150 as 2m:30s in QlikView

Displaying 150 as 2m:30s

Wed, 29 Sep 2010 14:11:15 GMT

Hi all,

I have an expression which evaluates to 150 and I want this displayed as 2m:30s. Dividing the expression by 86400 and then changing the number format to interval mm:ss almost works but the "m" and the "s" don't appear so to the user it's not obvious what this figure represents.

Does anyone know how to achieve this?

Many thanks.

Displaying 150 as 2m:30s

Miguel_Angel_Baeyens — Wed, 29 Sep 2010 14:19:51 GMT

Hello,

Use the interval() function so

=Interval(time('03:01') - time('01:00'), 'h:mm:ss')

will return "2:01:00"

Hope that helps.

Displaying 150 as 2m:30s

Wed, 29 Sep 2010 14:28:39 GMT

Thanks, but it still doesn't display the "m" and the "s" which is what I need.

Re. :Re: Displaying 150 as 2m:30s

martin59 — Wed, 29 Sep 2010 14:34:33 GMT

Hi,

You can try this :

=interval(150/(24*60*60),'m') & 'min ' & interval(mod(150,120)/(24*60*60),'s') & 's'

Hope that helps you

Displaying 150 as 2m:30s

Miguel_Angel_Baeyens — Wed, 29 Sep 2010 14:38:54 GMT

Hi,

A possible way -just thought of- is

=Replace(Interval(time('00:07:01') - time('00:01:42'), 'mm:ss'), ':', 'm:') & 's'

EDIT: There's no need to pass on timestamps:

=Replace(Interval(150/84600, 'mm:ss'), ':', 'm:') & 's'

will work too.

Displaying 150 as 2m:30s

Wed, 29 Sep 2010 14:48:06 GMT

OK, thanks for the suggestions. I had thought something along those lines would work but hoped there was a simpler solution. It's frustrating that the number cannot be formatted like in Excel to display however you want.

Thanks again.

Displaying 150 as 2m:30s

Wed, 29 Sep 2010 15:26:25 GMT

Although this works in a table, it doesn't seem possible to display numbers this way on the dimension axes of a chart. The best I can achieve is 02:30 by formatting the number as an interval. Is that right?

Displaying 150 as 2m:30s

Miguel_Angel_Baeyens — Wed, 29 Sep 2010 15:36:20 GMT

Hi,

That's right, since in Number tab in chart properties, format doesn't allow expressions, only format codes, and bot "m" and "s" are format codes.

Regards.

Displaying 150 as 2m:30s

Wed, 27 Oct 2010 10:44:30 GMT

Hi all,

This is the solution I used:

time((sum(TalkTime) / sum(CallsAnswered)) /86400, 'm') & 'm: '& time((sum(TalkTime) / sum(CallsAnswered)) /86400, 'ss') & 's')

I've discovered that this will cause an inaccuracy when sum(TalkTime) / sum(CallsAnswered) equates to (for example) 132.6 seconds which should display as 2m:13s but using the above expression will give 2m:12s. As far as I can see, using the above expression will only be out by a maximum of 1 second so it's not a big problem. Having said that, I believe that there can only ever be one correct answer so 1 second out is still inaccurate.

Does anybody know of a way to modify my expression to account for this?

Many thanks.

Displaying 150 as 2m:30s

nathanfurby — Wed, 27 Oct 2010 12:07:06 GMT

Try including milliseconds like this:

time((sum(TalkTime)/sum(CallsAnswered))/86400, 'm') & 'm ' & time((sum(TalkTime)/sum(CallsAnswered)))/86400, 'ss') & '.' & time((sum(TalkTime)/sum(CallsAnswered)))/86400, 'f') & 's'

If you want to increase the accuracy even further then change 'f' to 'ff'

Displaying 150 as 2m:30s

Wed, 27 Oct 2010 16:26:26 GMT

Nice idea but I still want the time to display as 2m:13s, not 2m:12.6s i.e. I want it rounded to the nearest second.

Displaying 150 as 2m:30s

nathanfurby — Wed, 27 Oct 2010 17:11:49 GMT

Then do this:

time((sum(TalkTime)/sum(CallsAnswered))/86400, 'm') & 'm ' & Num(time((sum(TalkTime)/sum(CallsAnswered)))/86400, 'ss') & '.' & time((sum(TalkTime)/sum(CallsAnswered)))/86400, 'f'),'0') & 's'

Displaying 150 as 2m:30s

johnw — Wed, 27 Oct 2010 18:24:31 GMT

Perhaps this?

dual(replace(interval(round(sum(TalkTime)/sum(CallsAnswered))/86400,'mm:ss'),':','m:')&'s'
,interval(round(sum(TalkTime)/sum(CallsAnswered))/86400))

The dual isn't strictly necessary, but I think it's best if your interval has an underlying numeric value instead of just being a text field.

Displaying 150 as 2m:30s

Thu, 28 Oct 2010 09:36:38 GMT

NathanFurby's solution seems to work until the expression equates to (for example) 179.9 which will return 2m: 60s.

John, your solution will return 3m: 00s which is exactly what I require.

Many thanks to everyone for their assistance.