Tracking active events over time

Question & Answer
, ,
1

I have a dataset containing four key values:
EventID, StartDate, EndDate, Category

Example CSV:
bee3c5f8-d93b-44c7-8a3a-8419f4af81ea, 2021-11-23, 2021-11-25, Red
f11ceb8c-853a-4f2b-81f6-ae1c99e60579, 2021-11-23, null, Blue
7ea0a73e-2f1d-4c05-a99c-49b118044bdf, 2021-11-26, 2021-11-27, Blue
93699642-f72a-4e6c-9055-5c39ed34efd9, 2021-11-27, null, Green
245b4171-1191-4cbf-b5a7-a7fdef451dbe, 2021-11-28, 2021-11-29, Blue
849251f7-3f0f-49ee-89fe-4e6034ddd141, 2021-11-29, null, Red
b51ca324-f165-4af7-833a-44d6cf1b8ded, 2021-11-29, null, Green

I would like to build a line chart grouped by Category with Time on the X axis and open event totals on the Y axis. The chart would display open events for every day over the past X days. An event is still open if the EndDate is null.

Using the above example CSV, I’d expect a line chart that plots each date over the past X days, grouped by category:
2021-11-23: 1 Red, 0 Blue, 0 Green
2021-11-24: 1 Red, 1 Blue, 0 Green
2021-11-25: 0 Red, 1 Blue, 0 Green
2021-11-26: 0 Red, 2 Blue, 0 Green
2021-11-27: 0 Red, 1 Blue, 1 Green
2021-11-28: 0 Red, 2 Blue, 1 Green
2021-11-29: 1 Red, 1 Blue, 2 Green

An event does not happen on each day, so I cannot use StartDate as the X axis. Are there other options to allow a running tally of each day?

2

To solve this objective, you would need to break down the data differently, and for each day with open record, you will have a dedicated record in your table. To do it 100% in QuickSight you would need to start with three table and merge them:

  1. A calendar table
  2. A color table
  3. You existing table
    Now, using the join mechanism you can create a cartesian product of all dates and colors (To merge between Calendar and Colors you will need to create a “dummy” column with the same value for both Calendar and Colors. Perform left-join to your original table to as well. Next you can create a calculated field that returns true if the date and color of the cartesian product match the record in your table. Then, you can filter only the records with the true value and end up the necessary format.

I hope it was clear enough to try it.

2 Likes