Announcements
cancel
Showing results for
Did you mean:
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:

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
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.

3 Replies
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.

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:

so strange 🙂

but thanks again.

Eli.

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.