Chart monthly count of events between start/end dates

I have a data set of events with the following relevant attributes:
*Event id
*Event handler (person)
*Event Start Date
*Event End Date (can be null for in-progress events)
*Event Category (enum)
I would like to visualize the count of events that a given handler is working on in each month in 2024. Additionally, I would like to group the count of events by event category.

The count of events can be done with counting {event_id}, this is a distinct id.
Event handlers also use distinct handler names, so I can create this as a control parameter for the dashboard.
In natural words, an event is active “when the start date is less than or equal to the month in question, and the end date is greater than or equal to the month in question”.

I am stuck on how to create a visualization that shows the months of this year (Jan through Dec) along the X axis, while using a duration (start and end date) instead of a single timestamp.

Hi @ostrichmayonnaiselog,

is this what you were thinking:

I used weeks instead of months and created separate visuals for handler and category.

For duration, I used a calculated field:

ifelse( isNotNull({event_end_date}), dateDiff({event_start_date}, {event_end_date}, 'DD'), dateDiff({event_start_date}, now(), 'DD') )

Does that help?


I created an arena dashboard with this here - sample_duration_from_ostrich_dataset


Thanks for your response!
Visually, I think Chart 2 is what I am looking for. However, I think there is a nuance that is still difficult for me to understand:
Given the following data:
|ID | Handler | Start Date | End Date | Size|

|— | — | — | — | —|
|102 | Meg | 2023-11-23 | 2024-01-09 | S|
|287 | Meg | 2023-12-30 | 2024-02-20 | M|
|312 | Meg | 2024-01-02 | | L|
| 333| Sue | 2024-01-17 | 2024-01-31 | S|
| 389| Bob | 2024-02-03 | | M|
| 401| Meg | 2024-02-21 | | S|

I would expect to see an X axis that starts at Jan and advances through Feb, Mar, etc.
As far as what I would expect to see in the data, let’s say I’m just viewing Meg’s data (i.e. we have filtered by a Handler parameter to only display Meg):
Jan would show a count of 3: (102, 287, and 312).
Feb would show a count of 3: (287, 312, 401).
Mar would show a count of 2: (312, 401).

If I create a vertical stacked bar chart with the X-Axis as the truncDate(“MM”, {End Date}), value as the duration that you provided (with the only adjustment being “MM” instead of “DD”), and Group/Color as {Size}, I get something close to what I’m looking for but the numbers are off (and March and April are missing).