Aggregate count per day in excel with open and close dates per record

Question

I am trying to find open records in a period of time. i have the open and close table on 1 table but not sure how to aggregate the count and a new table before i can graph out a YoY comparison. see sample data screenshot below and the goal. I am hoping to learn to do in excel and then ill try to replicate in tableau.

Answer 1

The easiest way to achieve this involves restructuring the data

For datasets with two separate fields or columns with open and close dates the easiest way to enable counts of open or active records is to reformat the data.

Instead of two columns/fields with dates, rewrite the data to have a single field containing dates of events (in this case "open" and "close"). Retain the case ID (or duplicate as many cases will now have two entries: one for open and one for close). Create a new column/field that classified the event (ie case open and case close).

The benefit of doing this is that the number of open cases in a time period is now a matter of counting the case IDs with an open but no close in the period (and adjusting, if you are not starting from zero, for the number open at the start of the time period). If you start a period with no open cases the number open at any time is simply the cumulative total open minus the total closed to that point in time.

The way to achieve the structure is to duplicate parts of the table. Once for the "open" event using the time for that in the new time column and once for the "close" event using the close date in the time column. Not forgetting to add the new column containing the open and close flag for each row.

The data structure should look like this:

Queries on this structure (grouped by even rather than case id) will find it much easier to identify open and closed cases or the total open cases at a given point in time.