topic Re: Covert seconds INTO Hours and a decimal for minutes in QlikView

Covert seconds INTO Hours and a decimal for minutes

rustyfishbones — Mon, 24 Feb 2014 17:28:08 GMT

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

instead of 496.5253.

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

Regards

Alan

Re: Covert seconds INTO Hours and a decimal for minutes

luis_pimentel — Mon, 24 Feb 2014 17:46:58 GMT

Hi Alan,

Could you upload a sample file with this issue?

Regards,

Luis.

Re: Covert seconds INTO Hours and a decimal for minutes

Gysbert_Wassenaar — Mon, 24 Feb 2014 17:48:33 GMT

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.

Re: Covert seconds INTO Hours and a decimal for minutes

Mon, 24 Feb 2014 17:54:14 GMT

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

Re: Covert seconds INTO Hours and a decimal for minutes

jerem1234 — Mon, 24 Feb 2014 17:55:00 GMT

Maybe you could also try:

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

Hope this helps!

Re: Covert seconds INTO Hours and a decimal for minutes

rustyfishbones — Mon, 24 Feb 2014 19:05:04 GMT

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