NHS Waiting times Part 3: Digging a little deeper with Pivot Tables

In my last two posts, I explained how I used filters and pivot tables to carry out some basic analysis of my spreadsheet on NHS waiting times.

Here, I’m going to talk though my further analysis of the data using pivot tables.

I decided to take another look at the spreadsheet showing the breakdown of how many patients had had to wait for what number of weeks, (from “>0-1” to “52 plus”), for each type of treatment, and in each hospital provider:

4 spreadsheet

I’ve underlined the ‘total’ row that appears at the end of the records for each provider, in the image above. When working with pivot tables, you either need to use the total rows alone or to remove them, to avoid the totals skewing the rest of the data. In this case I’ve begun by removing them as I explained in my last post on filters.

Again, it’s important to start with a question, or an idea of what you’re looking for. I decide to find out which provider is responsible for the largest number of patients waiting over six months for treatment. This means that I will want to find out which provider has the highest tally in the columns for “26-27” weeks until “52 plus” weeks.

To start with, I copied and pasted all of the data on provider, treatment function, and the number of weeks that treatment took for each patient recorded, into a new sheet. I left out the last few columns that I used in my last post (shown in the image below) – those that appear after the column “52 plus”, since they’re not important for the question that I’m trying to answer.

3 spreadsheet overview

I then hit ctrl + a and created a pivot table, as described in my last post.

18 pivot tables 2

Since I’m trying to find out which provider is responsible for the largest number of treatments being completed after 26 weeks, I then moved each of the fields after “26-27” into the values box.

19 pivot tables 2

By leaving the ‘treatment function’ breakdown out, the pivot table automatically presents me with a summary of the number of treatments for each “treatment function” completed. Er, I could have reached this point quite a bit sooner by just using the totals provided instead of taking them out with the filter but at least it’s been a good pivot table exercise.

Scrolling to the end of my pivot table, I enter a formula to total the number of treatments completed for each column over 26 weeks, for each provider.

20 pivot tables 2

After copying and pasting just this column into a new spreadsheet, next to the corresponding provider names, I can use the “sort largest to smallest” option in a filter to see which provider has been responsible for the most treatments taking over 26 weeks:

21 pivot tables 2

Maidstone and Tunbridge Wells NHS Trust seems to be the winner.

Out of interest, I then copied and pasted the existing column showing just treatments taking over 52 weeks, next to the provider name, in a new spreadsheet. Again, a bit of sorting with the filter reveals…

22 pivot tables 3

…a slightly different list, with Bart’s Health NHS Trust topping the list this time. Both it and King’s College Hospital NHS Foundation Trust were in the top ten providers taking longest over 26 weeks, but much lower down.

In my next post, I’ll have a go at visualizing both of these results, along with the one from my last post on which treatment function was responsible for the longest treatment times.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s