Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How-to convert timestamps to rounded hours?

Example, map:

11:23:32 --> 11:00

11:24:18 --> 11:00

12:01:18 --> 12:00

12:02:17 > 12:00

So I could quickly filter on events taking place within one same hour

Tags (1)
6 Replies
Highlighted
MVP
MVP

Re: How-to convert timestamps to rounded hours?

Create hours like:

Time(TimeStamp#(YourField, 'hh:mm:ss') , 'hh')

Highlighted
MVP
MVP

Re: How-to convert timestamps to rounded hours?

Something like below

=Time(Time# ('11:23:32','hh:mm:ss'),'hh')

or

=Time(Time# (YourTimeStamp,'hh:mm:ss'),'hh')

Highlighted

Re: How-to convert timestamps to rounded hours?

You have to write

=Time(Time# ('11:23:32','hh:mm:ss'),'hh')&':00'

=Time(Time# ('11:24:18','hh:mm:ss'),'hh')&':00'

=Time(Time# ('12:01:18','hh:mm:ss'),'hh')&':00'

=Time(Time# ('12:02:17','hh:mm:ss'),'hh')&':00'

Regards

Highlighted
Employee
Employee

Re: How-to convert timestamps to rounded hours?

What you want to keep in mind here is that a timestamp value and a time value is always represented by an underlying decimal value. In the decimal value the integer part represents the date, and the decimal component represents the time.

By using the Time() and Time#() functions you only alter the text part of the value, which means that the underlying numerical value will not be limited.

For example if we look at the time 11:23:32 the numerical value is 0.47467592... which can be retrieve by using the Num() function.

=Num(Time#('11:23:32'))

Now compare that with the time 11:00, by again using the Num() function, and it is apparent that the expected underlying numerical value for these two times are different. 11:00 corresponds to 0.458333...

=Num(Time#('11:00', 'hh:mm'))

Why is it important to consider the underlying numerical value?

The simple reason is that selections in QlikView are primarily applied in the underlying numerical value.

There can be several ways to tackle this issue in your data model, but one way is to extract the hour value and use it as base for creating time value.

=Time#(Hour(Time#( '11:23:32')), 'hh')

The numerical value is also as expected 0.45833...

=Num(Time#(Hour(Time#( '11:23:32')), 'hh'))

Highlighted
Master II
Master II

Re: How-to convert timestamps to rounded hours?

Try

HOUR(TimeStamp) as Hour

Regards

Alan

Highlighted
Employee
Employee

Re: How-to convert timestamps to rounded hours?

Hour() will only return the hour digit, not an actual time representation.