Hi,
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?
ws
I created an arena dashboard with this here - sample_duration_from_ostrich_dataset
ws
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).
Hi @ostrichmayonnaiselog,
Since you are using the end month as the x-axis, it is going to link all ID’s related to that end month. There is not another date value to reference the time between the start and end date.
You would need another date column added to the dataset for it to reference.
You could then utilize the a calculated field along the following lines:
ifelse(truncDate(‘MM’, {start_date}) <= truncDate(‘MM’, {updated_date}) AND truncDate(‘MM’, {end_date}) >= truncDate(‘MM’, {updated_date}), 1, 0)
The ID would need to exist within more than one date range so it can be attached to multiple dates. Then the ‘updated date’ would be the x-axis rather than the end date.
I will mark this as the solution but if you have any further questions, feel free to let me know.
Thank you!
