Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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))
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.
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.
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