Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

rsark
New Contributor III

How To create a calculated dimension using timestamp intervals

Hello,

I am attempting to create a bar chart with a calculated dimension that shows 30 minute increments using a timestamp field. I attempting to use the class function - class(minute(timestamp), 30) and I am not having any luck. Any help/suggestions would greatly be appreciated. Thanks

dimension result example: 6:00 AM - 6:30 AM, 6:31 - 7:00 AM, 7:01 - 7:30, etc

1 Solution

Accepted Solutions
MVP
MVP

How To create a calculated dimension using timestamp intervals

*smacks forehead* Ah, of course. I forgot about the date portion of the timestamp. We can eliminate it with frac(timestamp). Again, not sure this is the best way, but it seems to work. See attached.

=dual(timestamp(class(frac(timestamp) ,30/(24*60)),'h:mm TT') & ' - ' &

timestamp(class(frac(timestamp)+30/(24*60),30/(24*60)),'h:mm TT')

, class(frac(timestamp) ,30/(24*60)))



14 Replies
MVP
MVP

How To create a calculated dimension using timestamp intervals

First problem is that the minute() function returns an integer between 0 and 59. So your classification just gives you the first half of each hour vs. the second half of each hour.

It would be a simple matter to extract the number of minutes from the start of the day, timestamp*24*60, so you could do class(timestamp*24*60,30). You then have the right groups, but they'll display poorly, such as 960 <= x < 990.

You could instead stick with timestamps, like class(timestamp, 30/(24*60), but that looks even worse, as it shows the fraction of the day, not a timestamp format.

You can do a timestamp format, like timestamp(class(timestamp,30/(24*60)),'h:mm TT'), but then it only shows the starting time, not the ending time. Still, maybe that's good enough.

To get exactly what you're asking for, though, it looks like this does the trick:

=dual(timestamp(class(timestamp ,30/(24*60)),'h:mm TT') & ' - ' &

timestamp(class(timestamp+30/(24*60),30/(24*60)),'h:mm TT')

, class(timestamp ,30/(24*60)))

There might be a simpler way, but that's what I came up with.

You might be better off, though, calculating that in the script rather than at run time in a chart.

mov
Esteemed Contributor III

How To create a calculated dimension using timestamp intervals

Speaking of simpler way... If you have Date field in you application, you can use
=Date/48

MVP
MVP

How To create a calculated dimension using timestamp intervals

Put as simply as possible, since when does a date tell you what time it is?

Dividing a date by 48 turns 48 days into a single day, with each day being half an hour. That... makes no sense, and has nothing to do with solving this problem, unless I'm VERY confused.

rsark
New Contributor III

How To create a calculated dimension using timestamp intervals

Hi John,

Thanks for the quick reply. I found the expression you provided was very close to what I need, but it seems to be giving a result for each row. I would like to see the result with only 48 possible rows. Thanks in advance for your assistance/reply

MVP
MVP

How To create a calculated dimension using timestamp intervals

*smacks forehead* Ah, of course. I forgot about the date portion of the timestamp. We can eliminate it with frac(timestamp). Again, not sure this is the best way, but it seems to work. See attached.

=dual(timestamp(class(frac(timestamp) ,30/(24*60)),'h:mm TT') & ' - ' &

timestamp(class(frac(timestamp)+30/(24*60),30/(24*60)),'h:mm TT')

, class(frac(timestamp) ,30/(24*60)))



rsark
New Contributor III

How To create a calculated dimension using timestamp intervals

Thanks,

The solution John provided worked. I added the expression in resident table load and linked back to timestamp field. Smile

rsark
New Contributor III

How To create a calculated dimension using timestamp intervals

Thanks,

The solution John provided worked. I added the expression in resident table load and linked back to timestamp field. Smile

mov
Esteemed Contributor III

How To create a calculated dimension using timestamp intervals

John, you're right.
I didn't pay attention that it's about intervals. Tongue Tied

MVP
MVP

How To create a calculated dimension using timestamp intervals

Here's a slightly simpler expression that seems to do the same thing:

=dual(time(floor(timestamp, 1/48),'h:mm TT') & ' - ' &

time(ceil (timestamp, 1/48),'h:mm TT')

,floor(frac(timestamp),1/48))