Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rustyfishbones
Master II
Master 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
Gysbert_Wassenaar

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
luis_pimentel
Partner
Partner

Hi Alan,

Could you upload a sample file with this issue?

Regards,

Luis.

Gysbert_Wassenaar

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
Not applicable

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
Specialist II
Specialist II

Maybe you could also try:

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

Hope this helps!

rustyfishbones
Master II
Master II
Author

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