Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
EliGohar
Partner - Creator III
Partner - Creator III

Rounding time to the closest hour

Hi all,

I'm trying to round time to the closest hour using this expression:

time(floor(frac(STARTTIME),1/24),'hh:mm')

but something strange happens, it's partially working:

Untitled.png

you can see that the first row is wrong, the Hour (hh) field should be 04:00 PM, while the second row is ok.

What is the explanation for this behavior?

Thanks,

Eli.

1 Solution

Accepted Solutions
johanlindell
Partner - Creator II
Partner - Creator II

Hi,

There seems to be some "rounding error" in the very small decimals. FRAC for 16:00 gives 0,66666666666424 and not expected 0,66666666666667. If you add a very small number this should fix it.

=time(floor(frac(STARTTIME + 0.000001),1.0/24.0),'hh:mm') gives 16:00.

 

Num (STARTTIME) gives 43531.666666667 whereas Frac (Num(STARTTIME)) or Num (Frac(STARTTIME)) gives 0.66666666666424 whereas you would expect 0.666666667. 

View solution in original post

3 Replies
johanlindell
Partner - Creator II
Partner - Creator II

Hi,

There seems to be some "rounding error" in the very small decimals. FRAC for 16:00 gives 0,66666666666424 and not expected 0,66666666666667. If you add a very small number this should fix it.

=time(floor(frac(STARTTIME + 0.000001),1.0/24.0),'hh:mm') gives 16:00.

 

Num (STARTTIME) gives 43531.666666667 whereas Frac (Num(STARTTIME)) or Num (Frac(STARTTIME)) gives 0.66666666666424 whereas you would expect 0.666666667. 

EliGohar
Partner - Creator III
Partner - Creator III
Author

Thanks, @johanlindell !

I still don't understand why it works ok with ObjectID 10808 and not with 10807... 

This is the data I have in Oracle DB:

image.png

so strange 🙂

but thanks again.

Eli.

johanlindell
Partner - Creator II
Partner - Creator II

I would think this to be a bug in Qlik Sense. I guess the rounding error goes the other way there and it works because of that.