Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sharasridhar
Contributor III
Contributor III

Calculating Hospital Occupancy

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):

sharasridhar_0-1593632930072.png

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:

VisitIDArrival DateDischarge Date
AA126/1/20206/4/2020
AB136/2/20206/4/2020
AC146/3/20206/5/2020
AD156/4/20206/5/2020
AE166/5/20206/7/2020

 

I think i need a column called Occupancy:

VisitIDArrival DateDischarge DateOccupancy
AA126/1/20206/4/20201
AB136/2/20206/4/20202
AC146/3/20206/4/20203
AD156/4/20206/5/20201
AE166/5/20206/7/20202

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.

1 Solution

Accepted Solutions
JustinDallas
Specialist III
Specialist III

 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.

 

View solution in original post

4 Replies
dwforest
Specialist II
Specialist II

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
Specialist III
Specialist III

 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
Contributor III
Contributor III
Author

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
Specialist III
Specialist III

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'