5 Replies Latest reply on Nov 19, 2012 7:53 AM by Jonathan Drummey

# How do I calculate patient volume per hour per unique date of service?

Hello,

I'm trying to calculate the average patient volume per hour per unique date of service. I have about 501 lines documenting patient arrival and departure time, with many empty fields in the beginning due to difficulty in tracking patient departure. I'm having difficulty calculating the average patient volume per hour per UNIQUE date of service. For example, there were 32 patients that arrived at the clinic in the 12:00 to 12:59pm time block (Time In), but there are only 23 unique dates of service (DOS), since several patients arrived during that time block on some days. How do I display this as an average on a stacked bar chart in Tableau desktop? I would want the 12pm bar to read 1.39 (since 32 patients / 23 unique DOS =1.39). I've tried various methods, but I cannot seem to get the correct numbers to display. Do the blank fields have something to do with this? I'm attaching an example of the data. Any help or clarity that anyone can provide will be greatly appreciated.

-Annalee

• ###### 1. Re: How do I calculate patient volume per hour per unique date of service?

Zen Master Jonathan is the perfect guy to answer this question since he works in health care, and I'm pretty sure he has already worked through something similar to this. (This response will ping him.)

--Shawn

• ###### 2. Re: How do I calculate patient volume per hour per unique date of service?

Hi Annalee,

This is a bit of a messy problem for Tableau. Solvable, but messy. The issue is that in the data there are start times and end times, and those times can be longer than an hour. For example, an appointment might start at 5:30pm and end at 7:05 pm. Tableau doesn't know about durations, so if we're just looking at start & end times Tableau would end up missing the 6-7pm hour. The data needs to get padded out in order to calculate the number of patients in the clinic per hour for a given day, however this is a bit messier because a) the data has a DOS and times, and b) the data is at a finer level of granularity than the desired reporting level, I'm working out the latter. I should have a solution for you by tomorrow (Tuesday).

Jonathan

• ###### 3. Re: How do I calculate patient volume per hour per unique date of service?

Jonathan,

I think this could be a possible solution. Thank you very much for taking a look at this! I appreciate any help that you can provide.

-Annalee

• ###### 4. Re: How do I calculate patient volume per hour per unique date of service?

This is what I would like Tableau to display. To be more precise, I'm looking to get at the number of patients per date of service by Time In the clinic can expect to see. This will help answer important questions when it comes to staffing needs and times of day staffing may need to be ramped up.

• ###### 5. Re: How do I calculate patient volume per hour per unique date of service?

Hi Annalee,

There are a lot of different ways to look at # of visits/census/utilization in Tableau, based on your initial request I was going in one direction that (unfortunately) requires more effort to set up. For something like this, there are a couple of different views that are a lot easier to set up. They key is that we need the Date of Service in level of detail in the view, so the average can be computed over that. And since the view is at a higher level of detail (an aggregate of an aggregate), we use a table calculation to create the average.

In one case, the view duplicates what you're showing in the screenshot, however this view has a potential issue in that it's only showing the Avg # of patients per DOS where there were patients on that day/hour. The days when there are no patients aren't being counted. So I set up a second view that uses Tableau's "Show Missing Values" on the Day of DOS and that shows an average that is quite a bit smaller.

Jonathan