I have a dataset that looks like this, a list of events with event_start_date and event_end_date:
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
Thank you very much!