I have a table with event records, each event has a start and end date. How to display count of active events over time (X axis is time, Y axis is count of active events)?
Sample data:
event_id, start_at, end_at
1, 2022-01-31 03:00:00, 2022-02-10 06:00:00
2, 2022-01-31 02:00:00, 2022-02-20 05:00:00
3, 2022-01-01 01:00:00, 2022-02-15 08:00:00
4, 2022-01-01 08:00:00, 2022-02-01 13:00:00
Goal: plot a line chart with. X: Time by hour granularity Y: Count of active events
A few ways this can be solved. The simplest would be to place start_date in the y axis field well and do a count. However this would only provide a count of start_date. Are you wanting to count the number of events that have a start_date AND an and_date? Just trying to get a sense of the requirements so that your counts add up as expected.
Thanks for your help @todd.hoffman. I’ve edited the question and added sample data in it, hopefully this would make it a bit clearer.
Any follow up questions/clarifications? just let me know.
Thank you.
Are you wanting to use a line graph? If so just place your start_at in the X field well and do a count of event_id in your Y axis field well. You will probably need to adjust the date display format as well once you have your graph built.
Hi @mchafik. We hope the response from @todd.hoffman helped you. Let us know if this is resolved. And if it is, please help the community by marking the answer as a “Solution”. Many thanks.
Hi @mchafik - We hope the response from @todd.hoffman helped you. I am marking the reply as “Solution,” but let us know if this is not resolved.
Thanks for posting your questions on the QuickSight CommunityQ&A Forum!
Thanks @todd.hoffman abacon @apjvinod for your feedback.
start_at on the X axis, and count of event_id on the Y axis won’t solve it.
The question is a bit more complicated than that, here is how it can be done using Tableau (just as reference) if someone can help translate or find an equivalent approach using QuickSight.
Link: Displaying Active Records Over Time | Tableau Software
Thanks for your help, much appreciated!
Hello @mchafik - This seems to be more of a data preparation problem than a visualization. One initial thought that came to my mind of utilizing a Date-Time Dimension Table to solve this. I am trying to depict my thought process in the below snapshot. Please check once. Hope this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
