
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Round up time to the start of hour
How to round up time to the start of hour? Say,
Time Return
03:05 AM 03:00
07:45 PM 19:00
I want to know whether it's attainable by Set Analysis or by Data Load. Thanks!
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=time(hour(time#(TimeField,'hh:mm TT')),'hh:mm') as TIME

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
or
hour(time#(TimeField,'hh:mm TT'))&':'&00 as TIME

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this:
Time(Round(Time, 1/24), 'hh:mm') as Time
or
Time(Floor(Time, 1/24), 'hh:mm') as Time
Althouth I am not sure how you converted 7:45 PM to 17:00 -> did you mean 20:00 or 19:00 intead? First expression will get you 20:00 and second will get you 19:00


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try
Set TimeFormat = 'hh:mm TT';
LOAD
Time as TimeOriginal,
Time(Floor(Time, 1/24), 'hh:mm') as Time, // adapt the time format as needed, if you want to show AM/PM
...
FROM ...;
edit: this will of course perform a rounding to the lower hour value 07:45 --> 07:00

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
7:00 or 19:00 Stefan?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, should be 19:00 instead. Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this:
Time(Floor(Time, 1/24), 'hh:mm') as Time


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:
7:00 or 19:00 Stefan?
19:00. Missed the PM on the second line
07:45 PM --> 19:00
May, if the above doesn't work, please check that your input records are correctly read in as time, having a numeric representation, e.g. by using Time#() interpretation function or setting the default format.
See also following, also applies to time values:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, Sunny! You are right that it returns the rounded up time value but why are there duplicates?
I am intending this to use as a dimension, I expect that the measures will be aggregated according to the unique time value.
Any idea why it behaved like that?

- « Previous Replies
-
- 1
- 2
- Next Replies »