Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all! I have an issue I am really struggling with.
I have two dates, basically, a start date and an end date. The time between two dates, the event was open. I need to make a trend line to show how many events were open on a given date. So if the start date was 6/1/23 and the end date was 6/30/23, I need that to show open on my trend line for that period of time in between. Any ideas?
Thanks in advace!
Hi @jcollare,
If I get you right, you can create Event records for each date between Start and End Event Dates per Event.
Look at the code below:
Events:
LOAD * INLINE [
EventId, StartDate, EndDate
1, 6/1/2023, 6/30/2023
2, 5/28/2023, 6/3/2023
3, 6/29/2023, 7/1/2023
4, 6/28/2023, 7/5/2023
];
EventsExtended:
LOAD
EventId,
1 AS ActiveEventCounter,
Date(StartDate + IterNo()) AS EventActiveDate
WHILE
StartDate + IterNo() <= EndDate
;
LOAD
EventId,
Date(StartDate - 1) AS StartDate,
EndDate
RESIDENT
Events
;
Then you can create a line chart like this one:
The Dimension is the EventActiveDate and the Measure is Sum(ActiveEventCounter).
Please let me know if that covers what you are looking for.