7 Replies Latest reply: May 29, 2013 10:14 AM by David Ambrozie

# Round to closest half hour?

Which is the most simple way to (in the load script) to do a rounding of a timestamp field (hours:minutes) into "segmensts" like this:

Origin Rounded
11:39  11:30
01:34  01:30
03:11  03:00
05:43  05:30
03:27  03:30
06:16  06:30
03:54  04:00

Regards

Robert Svebeck

• ###### Round to closest half hour?

Fortunately, round() takes as base as parameter instead of decimal places, so you could do it like

Timestamp(round(Time, (1/48))) as RoundedTime;

Hope this helps,

Stefan

• ###### Round to closest half hour?

Great! It works, and I doubt that it can be done in a more simple way. :-)

• ###### Re: Round to closest half hour?

This indeed works perfect for rounding to half hours. Maybe a very simple question, but what is the calculation for rounding to full hours?

• ###### Re: Round to closest half hour?

Rounding to closest hour: Timestamp(round(Time, (1/24))) as RoundedTime;

Regards

Robert

• ###### Re: Round to closest half hour?

Thanks! Was thinking too dificult...

• ###### Re: Round to closest half hour?

round(time('02:45'), 'hh:mm') = time('03:00', 'hh:mm') AND MakeTime(3)

but

round(time('01:45'), 'hh:mm') <> time('02:00', 'hh:mm') OR MakeTime(2)

Due to numeric precision, calculated times may not align with time generated by QV time functions or database times. Not a problem if all your times are passing through the same arithmetic, but a potentially a subtle bug if they are not.

See

http://qlikviewnotes.blogspot.com/2011/10/correct-time-arithmetic.html

-Rob

http://robwunderlich.com

• ###### Re: Round to closest half hour?

I used class() function:

time(class(timestamp#(timestamp,'M/D/YYYY h:mm:ss TT'),MakeTime(0,30)))

Regards,

David