NHS Waiting Times Part 4 – Visualizing data

My last three posts have showed how I looked for a few simple stories in an excel spreadsheet on NHS waiting times, using filters and pivot tables.

I produced three results:

1. Data on which ‘treatment function’ was responsible for the most treatments taking over 18 weeks.

2. Data on which provider was responsible for the longest waiting times over 26 weeks (6 months).

3. Data on which provider was most responsible for making patients wait 52 or more weeks for treatment.

Continue reading


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.

Continue reading

NHS waiting times story – Part 2: Pivot Tables

Pivot tables are one of the most useful tools available for speedy data analysis – luckily they’re also quite easy to use, and with a bit of playing around you soon get the hand of it. They give you a way to reconstruct the data in your spreadsheet, interrogating it and almost asking it questions.

In my last post I introduced a dataset on waiting times for treatment from NHS providers. In this post I’m going to show how I (attempt to!) analyse the data to find a story, using pivot tables.

I started with the second sheet from my last post, which showed for each of 19 different ‘treatment functions’ or areas, (such as trauma or urology) within each provider: 1. the total number of completed courses of treatment, 2. the total number of courses with a known start date, and 3. the total number with a known start date that had been completed within 18 weeks:

6 pivot tables 2

18 weeks is the benchmark time limit for completing patient treatment, enshrined in the NHS constitution. It’s a target that was also reiterated by David Cameron in the summer of 2011 as part of his five NHS pledges.

So, subtracting the comments of each cell in column BK (treatments completed within 18 weeks) from its counterpart cell in column BJ (total number of completed courses of treatment) – ought to give us the number of completed treatments that were not completed within an 18-week period.

Continue reading

NHS waiting times story – Part 1: Filters

I had a quick go at pulling a few quick stories from a dataset from the NHS’s 2012-13 sets of data on treatment waiting times.

The data that I’m interested in shows the average waiting time for treatment at each of the hospitals listed.

2 Spreadsheet overview

On a separate sheet, it also shows for each of 19 different medical departments within each provider:

  1. The number of completed courses of treatment (completed pathways), it total.
  2. The total number of completed courses of treatment (completed pathways) where the date at which the clock started ticking is known -i.e. where it was recorded.
  3. The total number of courses of treatment with a known clock start date which were completed within 18 weeks.

1 Spreadsheet overview

On this sheet, the difference between the columns showing number 2 and number 3 (which are columns BI and BJ in the picture above), tells us the number of completed courses of treatment that took longer than 18 weeks.

Continue reading