Calculating Hospital Occupancy

[sharasridhar]

I have a dataset which records the VisitID, ArrivalDate, DischargeDate. I have got a request to build an app with visualizations to show the Number of patients admitted in the hospital on a given date.

At present I have a bar chart to present the total incoming visits which uses the ArrivalDate (that is 1 visit count per VisitID):

But, I need to take into account the duration of the stay and count the patient on all the days he is admitted in the hospital.

Basically I need to show on a bar graph the bed occupancy by the day of the week and not the count of incoming patients.

Dataset Example:

VisitID	Arrival Date	Discharge Date
AA12	6/1/2020	6/4/2020
AB13	6/2/2020	6/4/2020
AC14	6/3/2020	6/5/2020
AD15	6/4/2020	6/5/2020
AE16	6/5/2020	6/7/2020

I think i need a column called Occupancy:

VisitID	Arrival Date	Discharge Date	Occupancy
AA12	6/1/2020	6/4/2020	1
AB13	6/2/2020	6/4/2020	2
AC14	6/3/2020	6/4/2020	3
AD15	6/4/2020	6/5/2020	1
AE16	6/5/2020	6/7/2020	2

Occupancy in the above example is calculating the number of patients admitted the time of a new admission, including the new admission. Once I have this I am thinking I could use the Occupancy column to plot the graph using weekday(ArrivalDate) which would give me the total number of patients on a day of the week.

Please suggest if there is any other way to visualize the occupancy by day of the week, which can be filtered by year and month within the app if my idea is not ideal.

[dwforest]

I do this by creating a census flag for each patient for each day.

Use Interval match on Date from your Master Calendar and the Arrival an Discharge Dates (Or today() if not D/C), the a 1 as Census.

You should end up with a record per patient per day and then Sum(Census) will total for each day

[JustinDallas · Accepted solution]

 My relatively naïveté filled way of solving this would be to create a day row for every VisitId.  I have my dummy code for your dummy data below.

DATA:
LOAD *,
VisitID AS '%residency_date_key',
Date#([Arrival Date String],'M/D/YYYY') AS 'Arrival Date',
Date#([Discharge Date String],'M/D/YYYY') AS 'Discharge Date'
;
LOAD * Inline
[
'VisitID',	'Arrival Date String',	'Discharge Date String'
AA12,	6/1/2020,	6/4/2020,
AB13,	6/2/2020,	6/4/2020,
AC14,	6/3/2020,	6/4/2020,
AD15,	6/4/2020,	6/5/2020,
AE16,	6/5/2020,	6/7/2020,
]
;

DROP FIELDS "Arrival Date String","Discharge Date String"
;

ResidencyDates:
LOAD 
VisitID AS '%residency_date_key',
Date("Arrival Date" + IterNo(), 'M/D/YYYY') AS 'Residency Date'
RESIDENT DATA
WHILE "Arrival Date" + IterNo() <= "Discharge Date"
;


EXIT Script
;

Then, in my table, I have

Count([Residency Date])

To count the days in the hospital. 

The good thing about this method is that you can blend it with a Master Calendar with a Join Table, and then your WeekDay, Q1... analyses become a cake walk.

[sharasridhar]

Thank you for your quick response,

I have a followup question on your code. Will it show on each particular date the number of patients admitted?

[JustinDallas]

That's a different question.  But yes, it's simple to do that by adding a flag to the loop where you do something like

If( Iter() = 0, 'Y') AS 'Is Admittance Date'