Events on a chart using a time calendar

[morenoju]

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

[sunny_talwar · Accepted solution]

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]

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]

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

[morenoju]

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.

[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