Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
rustyfishbones
Honored Contributor II

Covert seconds INTO Hours and a decimal for minutes

Hi All,

I have calculated the number of seconds from one time to another.

I have a field called NewPickTime and when I sum the TOTAL seconds between 2 times I get an answer of 1787491

I want to convert this into hours and minutes, but as a decimal, so it would be 496.5253

However, when I try I keep getting weird numbers.

I have this in my script to calculate the number of seconds. This is correct and I have verified it.

INTERVAL(EndTime-TIME(RANGEMAX(EndPreviousTime, EndTime-RANGEMIN(PickTime, '00:05:00'))),'s') AS NewPickTime

When I create a text object and say =SUM(NewPickTime)/60/60 I get a result of

2014-02-24_1726.png

instead of 496.5253.

Does anyone have any ideas as to what I am doing wrong here?

Regards

Alan

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Covert seconds INTO Hours and a decimal for minutes

Your answer 1787491 is actually a value of 20.668 days formatted to be shown as seconds. Then interval function returns a number of days and formats it as seconds because of the 's'. So you're off by a factor of 24. Try multiplying NewPickTime by 24.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Partner
Partner

Re: Covert seconds INTO Hours and a decimal for minutes

Hi Alan,

Could you upload a sample file with this issue?

Regards,

Luis.

MVP & Luminary
MVP & Luminary

Re: Covert seconds INTO Hours and a decimal for minutes

Your answer 1787491 is actually a value of 20.668 days formatted to be shown as seconds. Then interval function returns a number of days and formats it as seconds because of the 's'. So you're off by a factor of 24. Try multiplying NewPickTime by 24.


talk is cheap, supply exceeds demand

View solution in original post

Not applicable

Re: Covert seconds INTO Hours and a decimal for minutes

Alan,

NewPickTime contains duration in seconds

Sum(NewPickTime) should also contain seconds but it is not the case: it will return a hh:mm;ss format. If you divide it by 3600, you will get very strange result (I did get strange results)

You need to use interval a second time: sum(interval(newpicktime, 's'))/60/60 to get hrs (but I think you need an extra /24 to get the part of the day)

Fabrice

jerem1234
Valued Contributor II

Re: Covert seconds INTO Hours and a decimal for minutes

Maybe you could also try:

num#(INTERVAL(EndTime-TIME(RANGEMAX(EndPreviousTime, EndTime-RANGEMIN(PickTime, '00:05:00'))),'s'))

Hope this helps!

rustyfishbones
Honored Contributor II

Re: Covert seconds INTO Hours and a decimal for minutes

Hi Gysbert,

Yes that was the problem, I had tried this earlier and I got the same result as what you suggested.

However I did not understand why I was getting it, now it makes sense

Thanks

Regards

Alan