9 Replies Latest reply on Oct 10, 2018 8:57 AM by Don Wise

# Calculating Number of Active Patients at any Given Time

Hello All,

I have a dataset of Emergency Department Patients that includes their arrival times and departure times (I cannot attach as it is healthcare information).  What I would like to do is build a highlight table that shows the # of patients in the department each hour so we can quickly see the busiest times in the department.  I would like the table to list the days of the week in rows and the hours of the day in columns.

I am having some trouble trying to figure out how to calculate how many patients are in the department while accounting for new patients arriving and others leaving.  For example, at noon there are 10 patients in the ER.  Between noon and 1PM 2 people arrive and 5 people are discharged so at 1PM we have 7 patients in the department.  I believe I will need to use running totals but can't seem to get the numbers to work out.

In addition, I would like to be able to adjust the dates that the table is looking at - which I think could be done easily using a parameter.

I did find this link:   https://kb.tableau.com/articles/howto/showing-records-that-fall-within-a-period-of-time but I'm not sure that it contains the information that I need to use.

I would really appreciate some help with this one.

Lisa

• ###### 1. Re: Calculating Number of Active Patients at any Given Time

Hi Lisa,

There is another Collection from kettan ..You would definitey get help in it.

Thanks

Deepak

• ###### 2. Re: Calculating Number of Active Patients at any Given Time

Hi Lisa,

You can try the attached workbook that I did a while back. See if the screenshot below matches what you're trying to do? Or use this thread:  Calculate No. of Patient Admitted and Discharged on each Day by Admission Date and Discharged Date  Use the last sheet as reference. If it answers your question, please mark this response as correct.  Thx, Don

• ###### 3. Re: Calculating Number of Active Patients at any Given Time

Hi Don,

Is your table keeping track of the total # of patient's the are present at any given hour?  I'm not sure that it does because there are a lot of blank cells.

Lisa

• ###### 4. Re: Calculating Number of Active Patients at any Given Time

Hi Lisa,

I think what you're trying to get to might be difficult...just the same I found the following thread which helped point me in a potential direction.  It plays out really well for using a day level of detail (I've included a screenshot but not the workbook for that effort).  Just the same I tried going to 'hour' level of detail and the attached workbook for that is what I got.  Here's the TB reference link:  https://kb.tableau.com/articles/howto/showing-records-that-fall-within-a-period-of-time

This effort requires multiple nested table calculations to get to what you might be looking for.  If anything it should point you towards some additional ideas...

Using 'day' to display number of patients active across multiple dates, using ED entries only.

Using 'hour' to display number of patients across multiple hours, using ED entries only. Filters for 'weekday' and mm/dd/yyyy.

The downside to this effort is that I can't verify the actual underlying data (Patient A is part of hour 1 and hour 2 and hour 3 etc.), likely due to the number of nested table calc's involved. You'd need to verify against your own data whether it works or not.

Regardless, hope it helps...thx, Don

• ###### 5. Re: Calculating Number of Active Patients at any Given Time

That is exactly the reference that I'm using Don!  I'm in the middle of trying to see if it works with my data now. I'll be happy if I can get my "hour" sheet to look like yours and the reference. I like how you used the filters for day, month, and year so I will try to replicate that also.

If I can verify that it works with my data the next step will be convert that line graph into a highlight table so we can look at the hourly census over a range of dates quickly but I guess one thing at a time. I'll let you know how it turns out.

I didn't realize this would be so complicated!

Many thanks!

Lisa

• ###### 6. Re: Calculating Number of Active Patients at any Given Time

So it actually worked, Don!!  Here is a pic of the completed line graph.  I did not yet add in your filters - just picked a range of dates in Sept.  Here is the result:

You can see that mid-day is the ED's highest volume.   Notice how the date increases in hour increments going horizontally.   Next step is to make a highlight table.  What I would like to do is have the days of the week running along the Y axis and 1 days' worth of data (hours 0-23_ running along the Y axis.  Since this data spans several days the x axis is very long and does not come back to the beginning of line graph each day - it is just one long range of continuous hours for 1 entire week, which is not particularly helpful.  My guess is that making the table I am envisioning would have something to do with the formula in the Active Number of Patient's field - or maybe the start date field- and when I click on edit calculation there is a field that asks how often you want to re-calculate but it is either greyed out or recalculate by "day" is not a choice.

I was able to make a highlight table of all the data in one long row but cant seem to the table I want from this.  What do you think?  Any ideas?

I wish I could send you my data but unfortunately I cannot.

Lisa

• ###### 7. Re: Calculating Number of Active Patients at any Given Time

Hi Lisa,

Glad you were able to make it through all of those nested table calculations; yes, they can be complex!

I know what you want to achieve in terms of view but I don't think you're going to get there as I tried with my set of data. The closest I could get to what you're looking for was to apply those (previous post) filters which would at least bring the view down to a more manageable level.

You can certainly try to bring in the Arrival Date on Rows using Arrival and setting the date to Weekday and give that a shot.  You'll likely end up with 7 individual line graphs for each day of the week; but then you'll need to further refine your view to filter down to a week's set of data...hard to tell because your set of data is somewhat different from mine. Keep experimenting tho!

• ###### 8. Re: Calculating Number of Active Patients at any Given Time

Lisa and Don,

Glad you got it worked out.

Late to the conversation, but just wanted to throw out some other

takes and discussions of this general type of problem:

• ###### 9. Re: Calculating Number of Active Patients at any Given Time

Thank you Swaroop!