Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Define time interval as a dimension

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Define time interval as a dimension

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)

5 Replies
MVP
MVP

Re: Define time interval as a dimension

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)

Not applicable

Re: Define time interval as a dimension

Thank you,

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

MVP
MVP

Re: Define time interval as a dimension

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

Not applicable

Re: Define time interval as a dimension

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

MVP
MVP

Re: Define time interval as a dimension

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.

Community Browser