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.
On a separate sheet, it also shows for each of 19 different medical departments within each provider:
- The number of completed courses of treatment (completed pathways), it total.
- 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.
- The total number of courses of treatment with a known clock start date which were completed within 18 weeks.
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.
Helpfully, clicking an unobstrusive little plus sign at the top of the spreadsheet opens about 52 more columns, showing exactly how many patients waited for what number of weeks with each provider.
One of the first things to note are the “total” rows that appear at the end of the data for each provider (underlined in red below):
It’s a good idea to remove these before attempting a pivot table using the rest of the data, (as I want to do). The alternative is to filter out everything else and to just analyse the totals.
Sticking in filters is easy:
- Click anywhere in the data and then hit ctrl + a.
- Click on the ‘data’ tab in the ribbon at the top, and then click on ‘filter’.
- A little drop-down triangle should appear at the top of each of your columns.
To filter out the totals:
- Click on the drop-down triangle at the top of the ‘Treatment Function’ column. This will open a small drop-down menu of all the values/categories in that column.
- Just un-tick the box next to ‘totals’. All of the total rows should immediately disappear from the data.