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:
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.
I then hit ctrl + a and created a pivot table, as described in my last post.
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.
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.
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:
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…
…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.