Chart showing last x hours of data only

[Anonymous]

Hello,

I am trying to make a bar graph of the count of a couple of different events. Each event (let's call them A and B) have a DateTime field associated with them in the format YYYY-MM-DD HH:MM:SS.MS. I'd like to create a graph which shows the count of the events for the last 8, 24 or 48 hours, by hour.

I can't for the life of me work out how to either get it from the current format of the DateTime field, or if I should be creating an EventTime (HH) field in the load script.

Pretty new to this so any help would be great!

Cheers.

[Chanty4u]

Hi ,

Have you tried using Hour() function:

=Hour(DateField) as Hour

[sunny_talwar]

You can work with the DateTime field of yours, but make sure that it is understood by QlikView as a datetime field.... I can use interpretation functions such as TimeStamp#() to help QlikView understand your timestamp if it doesn't already understand it as date time....

Now you still might need an hour field to be used as a dimension, but for the purpose of the expression, I would use set analysis to get past 8, 24 or 48 hours from your selected date and hour.

Dates in Set Analysis

Why don’t my dates work?

Get the Dates Right

The Master Time Table

[Anonymous]

Thanks for the detailed reply Sunny T.

I have separate date and time fields now, however I'm not sure how to 'tell' a chart that I only want to see the last x number of hours (let's start with 8 as an example). Is it a case of creating an expression that works out:

1. what hour it is currently

2. what date it is currently

3. to display the 8 hours before that time/date?

I have some experience with set analysis but not as much with variables - is it a good idea to create variables for current date/hour?

Many thanks

[sunny_talwar]

So may be something like this:

Based on Max TimeStamp

Sum({<TimeStamp = {"$(='>=' & TimeStamp(Max(TimeStamp)-MakeTime(8), 'DD/MM/YYYY h:mm:ss') & '<=' & TimeStamp(Max(TimeStamp), 'DD/MM/YYYY h:mm:ss'))"}>}Measure)

Based on current date and time

Sum({<TimeStamp = {"$(='>=' & TimeStamp(Now()-MakeTime(8), 'DD/MM/YYYY h:mm:ss') & '<=' & TimeStamp(Now(), 'DD/MM/YYYY h:mm:ss'))"}>}Measure)