Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
morenoju
Partner - Specialist
Partner - Specialist

Events on a chart using a time calendar

Hi guys,

I'm trying to display some "events" on a chart representing the day from 00:00:00 to 23:59:59.
I've created a time calendar for a variable called TimeOfDay. It's the one I want to have in the x-axis of a combo chart.

Times:
Load
  Time((RecNo()/60/60/24) + (IterNo() -1)) AS TimeOfDay
AUTOGENERATE 86399;

Probably using the continuous presentation as I always want to display the whole day from 00:00:00 to 23:59:59.
My events come with a timestamp, so I've separated date and time with the following lines in the load script:

LOAD 
EventID,
Date(floor("Timestamp")) as EventDate,
Time(frac("Timestamp")) as EventTime,

As a start, simply having a bar at the times of the day where there is at least one event would be enough. But it's not clear to me what measure to use.

I've tried:

If(EventTime=TimeOfDay,1)

The problem is that even though both times look like hh:mm:ss, I must have some rounding issue. I did the following workaround:

If(Text(EventTime)=Text(TimeOfDay),1)

It works. But only when I have one single event selected. And I need to draw in the chart as many as selected.

Any help on how to continue? What approach would you recommend me to follow?

Thanks much,

Juan

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

How about if you add an aggregation? Like Sum or Avg?

Sum(If(Text(EventTime)=Text(TimeOfDay),1))

or

Avg(If(Text(EventTime)=Text(TimeOfDay),1))

View solution in original post

6 Replies
sunny_talwar

How about if you add an aggregation? Like Sum or Avg?

Sum(If(Text(EventTime)=Text(TimeOfDay),1))

or

Avg(If(Text(EventTime)=Text(TimeOfDay),1))
morenoju
Partner - Specialist
Partner - Specialist
Author

That worked! Thanks!
By the way, do you think this is the best way to proceed? Or would it be better if in the load script I called both time variables (the one from the events and the one of the calendar) "EventTime"?
sunny_talwar

It gets tricky with the time because of the rounding issue... ideally, I would create The Master Time Table to do this... but in your case, a simple check of EventTime = TimeOfDay didn't work... so, I am not sure if you keep the names same... if it will work or not. But I would def. give it a shot.

morenoju
Partner - Specialist
Partner - Specialist
Author

Sounds good. I'll give it a shot, and may research more about rounding to the second if needed too. Thanks.
morenoju
Partner - Specialist
Partner - Specialist
Author

Using the EventTime directly looks very straight forward. I've used EventTime as dimension and Count(EventID) as measure.

The problem is that I'd need the whole day in the x-axis, not only the portion of the day where I have events. That's why I think I may need a calendar, although so far I don't know where. I may open a new conversation in the board later if I don't find any idea in the existing threads.

image.png

sunny_talwar

and that is exactly why you need The Master Time Table here... You probably don't have every second of a day which if why it isn't showing it. Adding the master time table should allow you to do that