Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi ,
Have you tried using Hour() function:
=Hour(DateField) as Hour
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.
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
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)