How to report number of ongoing events based on event start and end date

Question & Answer
, ,
1

Hi everyone!

I have a dataset that looks like this, a list of events with event_start_date and event_end_date:
image

I want to create a bar chart visual, with the number of “Ongoing Events” (not events started or closed, but ongoing), with the X axis aggregated by year, quarter or month.

I don’t which function gives the start date and end date of the aggregation period (the calculation would be the number of events that started before the end of each period (year, quarter, month), and ended after the start date of the period.

For example, if I aggregate by quarter, event #1 should be counted as ongoing in Q4 2022, Q1 and Q2 2023. If I aggregate by year, event #1 should be counted as ongoing in 2022 and 2023, and so on.

I don’t know also what date should be used in the X axis, as the X axis leftmost date would be the earliest event_start_date, and the rightmost date should be today.

How can one do this? If I didn’t manage to explain myself correctly please let me know :sweat_smile:

Thank you very much!

2

There is no way to create a date range field in quicksight. You can only use fields from your dataset.

If you want this, you would need to create something like a calendar in SQL and then join your dataset onto this calendar.

1 Like
3

Thank you very much @Max .

So for example imagine I have a gym, or a SaaS company, and I have a list of customers with the date they joined and the date they left.

If you had to create a report on the number of customers per month, how would you do it? Thanks!!

4

You would add the date timestamp to the x-axis and a distinct count of customer id as the value.

However, what you are asking for in the begging, you don’t have a timestamp of a general date. You only have dates when they joined and left. So you can only report on those dates. Not on generic dates.

1 Like