1 2 Previous Next 22 Replies Latest reply on Apr 6, 2012 8:09 AM by Alex Kerin

# Does anyone know how to create a utilization chart given a time frame and the capacity?

I have dates and times for example:

Check in - 8:00

Patient Left 9:00

The clinic is open from 8-5 (9 hours)...

So for example, I want to show that the hours of 8-9 we used 11.1% of the clinics capacity (1 hour / 9 hours available).

The graph i made in Excel is attached.

Thanks,

Ethan

• ###### 1. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Richard Leeke has done a couple of useful posts on this:

And here's one from Joe Mako that might also be of use:

Jonathan

• ###### 2. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Thanks Jonathan. Good info there, however I am still having trouble having the duration of minutes fall into an hour bucket.

For example:

An appointment from 8:00 - 9:05 should be...

60mins for 8:00am-9:00am

5mins from 9:00am-10am

Any suggestion

• ###### 3. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Just so I'm clear:

- 1 patient can be in a given room at a time.

- you are looking to track when a room is in use, from the chart in the spreadsheet you attached, the "when" looks to be at 5 minute increments or maybe less?

- on that increment (which will be the X axis), you are looking to graph # of rooms in use/total # of rooms?

This is more complex than I'd thought at first glance. I'm a little stumped how to do that at the moment, I'll have some time over the weekend to work on it. If anyone else on the forums has an idea I'd be glad to see it!

Jonathan

• ###### 4. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Hi Jonathan-

- Yes, 1 pt can be in a room at a given time. There can be multiple rooms though. So if a clinic has 5 rooms then you can have 5 pts in them, thus utilizing all your room capacity (100%). Furthermore, if there were say 4 pts. in a room @ 9:05 then your clinic at 9:05 would only be at 80%...

- Increments are at the minute

I am assuming I have to bucket my times...a sample excel data is attached with one pts record...

Any help would be AWESOME!

Ethan

• ###### 5. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?
Here's one attempt. I used Richard Leeke's notes from the links above to generate the queue, and then had to pad out the data to get the lines to draw properly since we're not just interested in the Check in/Patient Left events, but also the time in between down to the minute. I work with inpatient & outpatient data, and soon I'll be doing something like this for visits to the Emergency Department, so I made sure that visits can span midnight and entire days.
Since each room has no more than one person in it at a time, and we're trusting that the data is valid and doesn't have any overlapping rooms, just doing a running sum of Delta will tell us how many rooms are active at any given time. Here's the calc in the TC RS Delta field:
RUNNING_SUM(SUM([Delta]))
I'd put a Room Count in the data set, so to get the total number of rooms available in the TC WM Room Count:
WINDOW_MIN(MIN([Room Count]))
The Utilization Rate (TC WM Room Count) field is then:
[TC RS Delta]/[TC WM Room Count]
And generates a correct rate, for one facility at a time. In order to work when there are multiple facilities in the data set, I need to set up a Filter for one facility and the HHMM facility to get the padding data. I created a parameter to select the facility, and then a calculated field that returns 1 if the facility is equal to the chosen parameter value or the HHMM facility, and then filter for if that field = 1. I set this up as a proof of concept, but haven't put any data in for Facility B.
Workbook (with a bunch more notes in the captions) and sample data are attached. Does this work for you?
Jonathan
• ###### 6. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Jonathan-

I am eager to get to work tomorrow to check out the work you did. I will let you know as soon as I get in tomorrow to the office. I think from what you explained this could be it!

Ethan

• ###### 7. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

One thing that is important is that the original excel document I sent really needs to be the main data source (the information in Book 2 above). That is what populates the rest of my Tableau dashboard. Where would the data be entered to populate what you created?

Ethan

• ###### 8. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Hi Ethan,

The way I built it, the data would live in the same Excel workbook. I added three worksheets to the document, one a list of days, the other a list of hours:minutes, and the third was a table for facilities and room counts. The Custom SQL query would be added as an additional data source, so your Tableau workbook would have two datasources pointing to the same Excel workbook.

Joanthan

• ###### 9. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Jonathan-

WOW, wonderful. The only question I have is how the utilization is calculated. For example, let say there is only one room for the clinic. The main variable here is how long the clinic is open. If a patient is in the room say 60mins and the clinic is open for 8 hours they occupy the room for 12.5% of the total available clinic hours.

Is there a parameter control that can be added so that you can just add how many minutes the clinic is open for accross all the rooms. In the above example it would only be 8x60x1 but if they had two total rooms the denominator would be 8x60x2...

Ethan

• ###### 10. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Hi Ethan,

The view I created was oriented towards creating the chart in your original post, which was a utilization per office per minute. A utilization per clinic per day would be a different calc. The way I'd approach that would be like what you'd posted above:

(# of minutes clinic is in use) / (# of rooms) * (# of hours of operation) * 60 minutes/room

The denominator is fairly easy: there's already the # of rooms in the Facilities table, so you'd add an Hours of Operation field to the table, then to the Custom SQL, and create a calculated field for Tableau.

The harder part is the numerator, since from the data you posted in your second workbook it appears that what you have is atomic events and not a duration, so that would have to be calculated. The two ways I can think of doing this are to do a table calc in Tableau, or more Custom SQL.  The table calc would calculate a duration based on partitioning such as DOS/patient ID/room/facility, or a unique visit ID if that is available. I'm not sure how that would look, I think it would require a special sort on the event time so the first row in the partition would be the Check In and the last row in the partition would be the Patient Left, and then the table calc could be something like:

DATEDIFF('minute', LOOKUP([Event Time],FIRST()), LOOKUP([Event Time], LAST()))

Then that could be summed over the facility and day in a separate table calc to get a utilization rate. This would not be necessary if there was a duration in the visit record. So, my questions right now are:

1. Is the data file that you posted in Book2.xls all that you have for each visit?

2. How do you identify the clinic that the patient is in from your data?

3. Do you have any sort of unique visit ID?

4. Is there any duration of visit field available in the data?

Jonathan

• ###### 11. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Hi Jonathan-

Sorry to not have been as clear as I could have been. I think what you created is incredibly useful and really you did answer my first question as outlined in the original post. I guess I am no being greedy and also interested in utilization per clinic per day.

1. Is the data file that you posted in Book2.xls all that you have for each visit?

YES. We are doing time studies and the information collected in each separate clinic is the time stamp "Time Added"

2. How do you identify the clinic that the patient is in from your data?

This is unique to each clinic. So for example, Book 2 would be for lets say a Dermatology clinic

3. Do you have any sort of unique visit ID?

YES. The unique ID is the patient ID. In Book 2 all the rows (2-16) are for one patient with each Action ID as a separate step.

4. Is there any duration of visit field available in the data?

NOT Really. The duration of the patients total time in a room would be per patient (Patient Left - Roomed). In my current Tableau file a parameter was created to calculate total clinic available time.

Does this clarify? I am sad to say I am still lost...even though you are helping a great deal!

Ethan

• ###### 12. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Ok, thanks, one clarifying question re: #3:

Is it possible for more than one patient to have a visit at the same clinic in the same day? (This is why I was asking about a visit ID that would encompass the patient/clinic/time, where a given patient might have two appointments in the same day in your system).

And #4:

Originally you'd posted total time as Patient Left - Check In, is it really Patient Left - Roomed?

The way you have your data set up, you'd either need to set up a separate datasource for each clinic or to add two joins the Custom SQL (for Check In/Roomed and Patient Left activities) for each worksheet. I think the latter makes things easier because then you'd only need one batch of calculations, what works for you?

I'll be mostly offline for the rest of the day, I can take another swing at this tonight.

Jonathan

• ###### 13. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Oh, and one more question: what are the volumes of data (# of visits, # of clinics, # of rooms) you are working with?

• ###### 14. Re: Does anyone know how to create a utilization chart given a time frame and the capacity?

Q#3 - Yes, a patient may have 2 visits i the same day but rare. Fro example they may come to the clinic in the am but then leave for a MRI then return that afternoon.

Q#4 - Great catch. Yes Total time is Patient Left - Roomed. The reason is if we use Patient Left - Check in we would build in the wait time in the lobby, which does not factor in room utilization.

Volume if data is number of patients and # of rooms. We also will have 1 Clinic.

Ideally I want to only be able to enter data on sheet 1 in Book Two. I guess we could hide the HHMM and Days tabs if we cant build that into parameters within Tableau. We can probably exclude all information on Facility as it will always be only 1. Can we pull only from that file with custom SQL and create parameters for # of rooms and available clinic minutes? The fields highlighted in Yellow is data you can input. The others would be locked for users.

Eager to see what you come up with!!

Ethan

1 2 Previous Next