A birds eye view

My next blog is on another sample data file – The hospital data set (Click here to download the data file). This time, the data in excel was clean (though there were null values). The spreadsheet had 4 sheets.

1. ED visits

2. Patients

3. Ambulatory Visits

4. Discharges

5. Providers

I focused on analyzing data in sheets: ED visits, Ambulatory Visits, and Providers. I made joins to create the relationships.

When I first saw the Ambulatory Visit sheet, my first focus fell on the difference in time between the appointment scheduled date and the visit date. Patients prefer to get the earliest appointment to seek service from the provider. No one wants to wait that too especially being sick. So let’s create a calculated field to find the time difference.

Let’s take the Visit Type in the column and pull the above-created calculated field to the text in the marks pane. Convert the measure from the sum to the average. Now we get a quick text table with the average days to get an appointment by visit type (Follow up, New, Physical, and Telemedicine). Edit the title and format the border to give a clean look.

On average it takes a minimum of 15 to 16 days to get an appointment. There can be many reasons for such a long gap. I have listed 3 probable ones below.

1. Inadequate staff/service provider

2. Provider availability per day( If the provider is available for say 3 hours then he can see very few patients and this added up the queue).

3. Inadequate space or in other words the facility might not have the capacity to accommodate more people per day.

A probe into the root cause of the above might help in increasing the quality of service provided to the patients.

Next, my analysis shifted to the Visit Status (Completed, No Show, and canceled) of the patients.

For this, a dual-axis pie chart (donut chart) helps us to visualize better. We need 2 measures to create an axis. So create a calculated field 1 and add the min (1) to the rows and visit type in the column. Add one more min (1) in rows, making it a dual axis. At first, it gets clumsy. Change the color to white for min (1)2, reduce the size, and now we get a donut shape. Add the count (patient MRN) and visit type to text and do the required formatting to get a neat view. Add visit status to text in min (1) marks pane.

Add visit type as a filter. Make it entire view, and change the title of the sheet as desired.

In each Visit Type, some patients did not show up to the appointment or canceled. The canceled visit problem can be addressed by scheduling another appointment. The reasons patients did not show up for the visit may be

1. They are not happy with the quality of service received from the provider

2. They might not be happy with the wait time

Finding the root cause of the above two problems can improve the quality of the service provided by the provider to their patients.

Next which provider attended more patients? For this, we need to create a parameter to find top N.

To create this bar chart, take count(patient MRN) in column and year(dateofvisit) & provider name in the row. In marks pane, place the provider specialty in colors and count(patient MRN) in the text. Add visit status, provider specialty, and provider name to filters. Create a parameter with top N.

Sally Sue of Pediatrics attended to more patients. A further drill-down chart can tell us how many patients she attended in each visit type.

It's time for us to display the above findings in a dashboard view.

Create a new dashboard. Make it to floating or it's always wise to add them to a container so they stay and look good.

Next, we start analyzing the ED visit sheet. The ED is the most important department in hospital care. The average length of stay of a patient in ED determines the efficiency of ED. The LOS is calculated using the datediff function. Create a calculated field LOS as below.

I created a lollipop chart for a better visual. A lollipop chart is a dual-axis bar chart. For a dual axis, we need 2 measures. Pull avg(LOS) twice in the column shelf. Then add the reason for visit in the row shelf. Make it a dual-axis. In the marks pane, add the reason for visit in colors. Make one avg(LOS) to the bar chart and reduce the size of the bar lines. Make another avg(LOS) to circle chart. Adjust the size to resemble like a lollipop. Add avg LOS in text. Format to make them display inside the circle. Finally, sort the reason for visit in column shelf to descending.

Another important measure to consider is patient’s Acuity. Higher the acuity more care the patient needs. Analyzing acuity can help us determine the number of nurses and Doctors needed in Emergency care as both acuity and patient care are directly proportional. Add the reason for visit in the column shelf. Count(patient MRN) in the row shelf. Pull the measure acuity to colors in mark pane. Add acuity to filter and show the slider with all values.

Finally move both the charts to dashboard to give a good view.

Create a story and pull both the dashboards. For more information please visit my tableau public profile to download the workbook.

The more we slice and dice the dimensions with the measures, the more granularity we get. More granularity throws more light to our data analysis. Small formatting like addition of tool tip, text display in the chart, giving boarders, etc will give us a neat look to our visualization. Remember that tableau does what the user instructs. So when using calculated field make sure to use the right logic. The NULL values are always a blind spot.

Data analysis aims in identifying the root cause of problems and finding a solution to the same in order to provide quality business improvements. Identifying a process improvement and putting it into practice brings quality service to customers and the end is always a happy ending.