topic Define time interval as a dimension in QlikView

Define time interval as a dimension

Thu, 23 Apr 2015 11:03:28 GMT

Good morning/evening everyone !

I tried today to define as a dimension time intervals having the date of events in a table in excel. I want to count the number of events that happened between 8h and 8h for instance. What is the dimension to define.

Tahnk you

Re: Define time interval as a dimension

swuehl — Thu, 23 Apr 2015 11:17:53 GMT

So you have a field containing date and time, and this field is already interpreted as timestamp by QV when reading in?

And you want to count all records falling into a 24 hours period starting from any date's 8 am, ending 8 am the day after?

Then look into daystart() function, something like

LOAD

Event,

EventTimestampField,

daystart(EventTimestampField, 8/24) as Bucket,

....

FROM ...;

Then create a chart with dimension Bucket and expression

=count(Event)

Re: Define time interval as a dimension

Thu, 23 Apr 2015 11:21:07 GMT

Thank you,

Does it consider the past dates? And how about format?

Re: Define time interval as a dimension

swuehl — Thu, 23 Apr 2015 11:28:02 GMT

Not sure what you mean with past dates. It should handle all dates the same, no difference between past and future dates except you have some logic in your script to do so.

Format is important, there are some classic blog posts by Henric that explain why and how to handle, e.g.

Get the Dates Right

The Date Function

Re: Define time interval as a dimension

Thu, 23 Apr 2015 11:29:38 GMT

Also, how can I make QV recognize my date as a timestamp?

Re: Define time interval as a dimension

swuehl — Thu, 23 Apr 2015 12:43:49 GMT

That's hard to answer without knowing your input field format.

When you look at your field values using e.g. a list box, how are the values displayed?

And when hovering over the field in table view, what meta data are displayed? Does QV recognize the field as Date or Timestamp, having a numeric representation?

Please check Henric's two blog posts, they should explain how to load date / time values just fine.