How to look at open items between two dates

[jcollare]

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!

[marksouzacosta]

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.