topic Re: Converting number (already hrs) to time format in QlikView

Converting number (already hrs) to time format

Anonymous — Fri, 12 Jun 2015 18:07:38 GMT

Hello Everybody,

I have a calculation where I sum up numbers (in s) to hours (by dividing by 3600) so something like this:

((sum(if(LabelField1='sec',valueField1)))/3600)

Which returns a number like 51.473

The customer wants to see this now in the format hh;mm;ss though.

When changing the presentation to Time i get 11:21:36

Also, since I already calculate it in hrs that change would be wrong anyway.

Does anybody know how to convert the 51.473 (hrs) to hh;mm;ss time format?

maxgro — Fri, 12 Jun 2015 18:12:53 GMT

51.473 / 24

then use interval to format

interval (51473/24, 'hh:mm:ss')

Anonymous — Fri, 12 Jun 2015 18:37:46 GMT

so you mean change the formular to:

Interval((sum(if(LabelField1='sec',valueField1)))/3600,'hh:mm:ss')

because this is not changing anything.

Fri, 12 Jun 2015 18:58:44 GMT

Interval((sum(if(LabelField1='sec',valueField1)))/(3600*24),'hh:mm:ss')

This will change your time to number of days (before decimal part) and after decimal part will be time part.

maxgro — Fri, 12 Jun 2015 19:17:21 GMT

interval(((sum(if(LabelField1='sec',valueField1)))/3600) / 24, 'hh:mm:ss')

or expression without interval, format as interval in number tab

((sum(if(LabelField1='sec',valueField1)))/3600) / 24

MarcoWedel — Fri, 12 Jun 2015 19:39:39 GMT

Interval(Interval#(Sum({$<LabelField1={'sec'}>} valueField1) ,'s'))

hope this helps

regards

Marco

Anonymous — Fri, 12 Jun 2015 20:13:42 GMT

Thanks Massimo... I used the second option.. Was easier

MarcoWedel — Fri, 12 Jun 2015 21:14:29 GMT

one performance comparison between the initial Sum(If()) expression and a set expression version:

test data:

set expression:

Sum(If())

hope this helps

regards

Marco