Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Re: Rounding time to the closest hour

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

EliGohar

Partner - Creator III

2019-04-22
05:30 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

2,240 Views

1 Solution

Accepted Solutions

johanlindell

Partner - Creator II

2019-04-22
06:10 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

johanlindell

Partner - Creator II

2019-04-22
06:10 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2019-04-22
09:44 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

2,204 Views

johanlindell

Partner - Creator II

2019-04-22
12:59 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

2,197 Views

Community Browser