topic How do i convert to HH:MM in QlikView

How do i convert to HH:MM

Anonymous — Tue, 21 Dec 2010 01:20:30 GMT

I have a chart which display the runtime in minutes as 69.77hrs, but the actual value is 69hours 46 minutes

Is there a way in Qlikview where we can convert the value in HH:MM format?

Thanks!

How do i convert to HH:MM

pover — Tue, 21 Dec 2010 01:37:06 GMT

Divide your number by 24 and then use the interval function:

=Interval(69.77/24,'HH:mm')

Regards.

How do i convert to HH:MM

Anonymous — Tue, 21 Dec 2010 18:33:59 GMT

My expression is

SUM

(run_time_minutes/60) = 69.77,

if i write =interval(

(SUM

(run_time_minutes/60) /24),'HH:MM'), i am not getting the value i expected.

How do i convert to HH:MM

matt_crowther — Tue, 21 Dec 2010 18:53:35 GMT

Yee-Haw; we're in real cowboy work-around country with this solution - I'm sure there'll be an easier way but as I don't have your actual data it's the only thing I could test:

=floor(69.77,1)&'Hrs '&round((60*(1-((ceil(69.77,1))-69.77))),1)&'Mins' result = '69Hrs 46Mins'.

Replace the '69.77' with the result of your initial formula - essentially it's working out what 77% of 1hr is - the magic 46mins.

Hope that helps,

Matt - Visual Analytics Ltd

How do i convert to HH:MM

Miguel_Angel_Baeyens — Tue, 21 Dec 2010 19:42:26 GMT

Hello,

Check that the format is case sensitive:

=Interval('69,77'/24, 'hh:mm')

Hope this helps

How do i convert to HH:MM

Tue, 21 Dec 2010 19:46:11 GMT

I've added Num() to your field name because I'm not sure that it is being interpreted as a number. If it is, you can remove the num() around run_time_minutes. This also makes the assumption that the value for run_time_minutes only has two decimal places. If it has more, you could consider using subfield. It's more process intensive though so I'd avoid it unless you have to.

interval#(floor(num(run_time_minutes))&':'&(round(num('.'&right(num(run_time_minutes),2))*60)),'hh:mm')

How do i convert to HH:MM

Anonymous — Tue, 21 Dec 2010 21:17:27 GMT

Thank you , this option works , could you please let me know how do i make the format look like 69:46 instead of '69Hrs 46Mins'

How do i convert to HH:MM

Tue, 21 Dec 2010 21:49:22 GMT

Sorry, Not sure. I haven't seen that before.

How do i convert to HH:MM

pover — Tue, 21 Dec 2010 23:13:37 GMT

You can do

=floor(69.77,1)&':'&round((60*(1-((ceil(69.77,1))-69.77))),1)

to get that format.

It's a shame the interval() function didn't work. What result did you get by trying

interval((sum(run_time_minutes/60)/24),'HH:mm')?

Regards.

How do i convert to HH:MM

Anonymous — Wed, 22 Dec 2010 02:08:01 GMT

FOR EXAMPLE : if the value is 52hrs 44min i get result 12

Thank you!

How do i convert to HH:MM

johnw — Wed, 22 Dec 2010 02:18:58 GMT

You mean the value is 3164? So a whole number of minutes? Because '52hrs 44min' isn't a number.

A text box with this:

=interval((3164/60)/24,'hh:mm')

Gives this result:

52:44

Your problem is not with the interval() and how people are telling you to do it. Your problem lies elsewhere. Perhaps the value isn't actually 3164, for instance. For that matter, it is IMPOSSIBLE to get result '12' with the expression Karl gave you, because the format will insert a colon. So maybe you got '00:12' or '12:00', but you didn't get '12'. Or if you DID get '12', you aren't using Karl's expression.