How to Combine Multiple Date Fields into One Timeline for Event Tracking?

Question & Answer
, ,
1

I would like to know if the following is possible in QuickSight:

I have a dataset some_user_data with the following fields:

id created_at updated_at marked_at deleted_at
1 2025-01-16T19:56:34.000Z 2025-01-16T19:56:34.000Z NULL NULL
1 2025-01-16T19:56:34.000Z 2025-01-19T19:56:34.000Z 2025-01-19T19:56:34.000Z NULL
2 2025-01-27T20:22:28.000Z 2025-01-27T20:22:28.000Z NULL 2025-01-27T20:29:28.000Z
2 2025-01-27T20:22:28.000Z 2025-01-27T20:22:28.000Z NULL NULL
3 2025-01-28T20:22:28.000Z 2025-01-29T20:22:28.000Z NULL 2025-01-29T20:22:28.000Z
3 2025-01-29T20:23:28.000Z 2025-01-29T20:23:28.000Z NULL NULL

Each record in my dataset has one or more relevant dates associated with it, including:

  • created_at, updated_at (always present)
  • marked_at, deleted_at (may or may not exist)

I want to create a line chart where I display the number of records by date, but I need to combine all these dates into a single timeline (X-axis) while differentiating the event types (Created, Updated, Marked, Deleted).

My Expected Outcome:

  • X-Axis: A single combined timeline (from created_at, updated_at, marked_at, deleted_at).
  • Y-Axis: The count of records for each event (Created, Updated, Marked, Deleted).
  • Color/Legend: Different colors or legends to differentiate event types.

Any advice or suggestions on how to achieve this in QuickSight would be greatly appreciate

2

Hello Maxim,

I think may be you would need to fix that at the backend level or via Custom SQL to combine the date for each rows such as union.
yes there will huge number of rows and for any duplication you should be able to filter out at the dashboard level.
this way you may be able to count the events .

For some quick ideation see -How can I merge Creation and Deletion count from two different date series in a pivot? - #2 by robdhondt

Hope this helps.

Cheers,
Deep

1 Like
3

Hi @maxim,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!