I would like to use Tableau to display how well different programs are utilizing their bed inventory over time. Each program has a set number of beds that can be made available to people who need them. People enroll with programs at different times, so each client has a unique enrollment start and end date (e.g., Client 1 enrolls with a program and has a bed from 1/1/2020 and leaves the program on 9/1/2020). At the same time, the program has dynamic inventory, meaning that the number of beds available can change throughout the year (e.g., 10 beds are available from 1/1/2020 through 1/31/2020, but 20 beds are available from 2/1/2020 to 6/1/2020).
The way I calculate utilization is to look at the number of nights a bed was used by a client versus the number of nights a bed is available through a program. For example, let's say a program has 10 beds available from 1/1/2020 to 1/31/2020. That means there were 10 beds available for 30 nights, or 300 "bed nights" in the period. During that period, there were 8 clients with enrollment start and end dates overlapping with this inventory period in different ways. One client was in the program from 1/1/2020 and left on 1/11/2020 (10 bed nights used), another client was in the program from 1/15/2020 and left on 1/20/2020 (4 bed nights used), and so forth for the other 6 clients. Let's say that in total, clients used 250 bed nights between 1/1/2020 and 1/31/2020. That results in 250 bed nights used out of 300 bed nights available in the given period, or a utilization rate of 83.3%. This utilization rate is what I want to calculate and display over time in Tableau.
The complicated part of this effort is that client enrollments and inventory availability both change over time. That means that I can have a client actively using a bed for several months while the available inventory is changing. Also, clients and inventories may have NULL end dates because they are currently active (or at least were active at the point the data was collected).
How can I dynamically calculate utilization rates for this use case? I have explored date scaffolding, but all of the examples I've found so far rely on only 2 dates, not 4 (as is the case with my data).
Here are some example client data (that should be paste-able within text document):
client id,program id,enroll_start,enroll_end
1,1,1/1/2020,9/1/2020
2,1,6/1/2020,
3,1,1/1/2021,7/1/2021
4,1,6/1/2021,3/1/2022
5,1,6/1/2022,
6,1,1/1/2020,
7,1,6/1/2020,9/1/2020
8,1,1/1/2021,
9,1,6/1/2021,12/1/2021
10,1,6/1/2022,
11,2,1/31/2020,10/1/2020
12,2,7/1/2020,
13,2,1/31/2021,7/31/2021
14,2,7/1/2021,3/31/2022
15,2,7/1/2022,
16,2,1/31/2020,
17,2,7/1/2020,10/1/2020
18,2,1/31/2021,
19,2,7/1/2021,12/31/2021
20,2,7/1/2022,
21,3,1/11/2021,
22,3,1/1/2020,6/5/2020
23,3,3/1/2020,6/5/2020
24,3,4/1/2020,6/5/2020
25,3,5/1/2020,6/5/2020
26,3,1/1/2022,
27,3,2/1/2022,
28,3,3/1/2022,
29,3,4/1/2022,
30,3,5/1/2022,
Here are some example program inventory data:
program id,inventory_start,inventory_end,beds
1,1/1/2020,12/31/2021,8
1,1/1/2021,12/31/2022,10
1,1/1/2022,,8
2,1/15/2020,12/15/2020,15
2,1/1/2021,,10
3,1/1/2020,6/5/2020,5
3,1/2/2022,,10
Any help would be much appreciated!
