Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
=time(hour(time#(TimeField,'hh:mm TT')),'hh:mm') as TIME
or
hour(time#(TimeField,'hh:mm TT'))&':'&00 as TIME
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
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
7:00 or 19:00 Stefan?
Sorry, should be 19:00 instead. Thanks!
Try this:
Time(Floor(Time, 1/24), 'hh:mm') as Time
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:
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?