topic How To create a calculated dimension using timestamp intervals in QlikView

How To create a calculated dimension using timestamp intervals

rsark — Wed, 13 May 2009 00:39:30 GMT

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

How To create a calculated dimension using timestamp intervals

johnw — Wed, 13 May 2009 01:28:54 GMT

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.

How To create a calculated dimension using timestamp intervals

Anonymous — Wed, 13 May 2009 01:32:08 GMT

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

How To create a calculated dimension using timestamp intervals

johnw — Wed, 13 May 2009 02:11:35 GMT

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.

How To create a calculated dimension using timestamp intervals

rsark — Wed, 13 May 2009 03:05:52 GMT

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

How To create a calculated dimension using timestamp intervals

johnw — Wed, 13 May 2009 03:20:17 GMT

*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)))

How To create a calculated dimension using timestamp intervals

rsark — Wed, 13 May 2009 03:27:46 GMT

Thanks,

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

How To create a calculated dimension using timestamp intervals

rsark — Wed, 13 May 2009 03:27:54 GMT

Thanks,

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

How To create a calculated dimension using timestamp intervals

Anonymous — Wed, 13 May 2009 03:32:20 GMT

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

How To create a calculated dimension using timestamp intervals

johnw — Wed, 13 May 2009 03:34:31 GMT

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))

How To create a calculated dimension using timestamp intervals

johnw — Wed, 13 May 2009 03:42:44 GMT


Michael Solomovich wrote:John, you're right.
I didn't pay attention that it's about intervals.

Ah, well. I was hoping I was missing something really brilliant, since I think my solution was pretty clucky.

How To create a calculated dimension using timestamp intervals

rwunderlich — Wed, 13 May 2009 13:07:03 GMT


John Witherspoon wrote:
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))<div></div>

John,

That's a very elegant expression. It solves a problem for me where I want to let the user choose an interval without having to precalulate the intervals. I extended your idea to use a field selection where the user selects an interval. Even finer control could be given by using a variable. Also creates some interesting possibilities for flexible drill down groups.

=aggr(dual(time(floor(timestamp, 1/"Interval Size"),'h:mm TT') & ' - ' &
 time(ceil (timestamp, 1/"Interval Size"),'h:mm TT')
 ,floor(frac(timestamp),1/"Interval Size")),
 timestamp)

An example is attached. Thanks for the idea!

-Rob

How To create a calculated dimension using timestamp intervals

peter_turner — Mon, 19 Apr 2010 16:04:29 GMT

Hello,

I found this thread very interesting and have had a look at the examples from Rob and John.
I've extended the interval down to 10min blocks, but would also like to increase the interval size to include 7,14 and 30 day 'blocks'.

Does anyone have any hints to get me started?

Thanks,
Peter.

How To create a calculated dimension using timestamp intervals

Fri, 09 Dec 2011 05:09:11 GMT

Hi, As i m using the personal edition , not able to open your provided file. getting error that its only for licenced user.

please help.

Regards

Ravindra Mandre

Re: How To create a calculated dimension using timestamp intervals

Fri, 09 Dec 2011 06:00:54 GMT

Hello John , your file can be used only with licenced version of qlikview , i am using personal edition .

please help me on this.

Ravindra Mandre