Rounding time to the closest hour

[EliGohar]

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.

[johanlindell · Accepted solution]

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]

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.

[johanlindell]

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.