Days Open Counter

Question & Answer
, ,
1

I have been trying to find a solution to this issue for sometime. I have events that have created and completed dates. What I need to do is provide a daily count of how many events are open each day.

Event_id date created date completed
asd 11/4/2024
fgh 11/1/2024 11/2/2024
jkl 10/31/2024 11/3/2024
qwe 11/1/2024 11/1/2024
rty 10/28/2024

I am hoping to get an output similar to below:

Event_id date created date completed 10/28/2024 10/29/2024 10/30/2024 10/31/2024 11/1/2024 11/2/2024 11/3/2024 11/4/2024 11/5/2024
asd 11/4/2024 Y Y
fgh 11/1/2024 11/2/2024 Y Y
jkl 10/31/2024 11/3/2024 Y Y Y Y
qwe 11/1/2024 11/1/2024 Y
rty 10/28/2024 Y Y Y Y Y Y Y Y Y

This would allow me to count how many events were open each day.

2

Ho @andyhob
do you have a “time table”, with all dates, joined to your dataset?
BR

1 Like
3

No I do not. It’s a live running table.

4

I do have a date table that continuously logs the date. It is joined to other data sets. It looks like this.

image
image1136Ă—172 11.4 KB

5

You would need to join the “date” table to the “event” table, to get all days.
Then you could try do calculate something like

ifelse( date.date >= event.datecreated AND ( date.date < event.datecompleted OR ISNULL(event.datecompleted)), "Y", NULL)
1 Like
6

Hi @andyhob,
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 go ahead and close out this topic.

Thank you!

7

Hi @andyhob,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!