Home   About Kurtosis   Courses   Course Calendar   Booking Information   Ideas   Technique   Clients   Contact

Counting outliers

How to keep track of how many hospital inpatients are in the wrong beds

If you already know how to count outliers, this will appear clunky, and you've probably got an altogether slicker, more tech-savvy way of doing it. In which case ignore this article. But if you don't already count outliers, here is a reasonably quick way of achieving it using Excel.

Use this Excel workbook in conjunction with the text: Counting Outliers.xls (3.74Mb).

Step 1

Take a ward stay extract that covers the period you are interested in. For the purposes of this example we’ll take the two-month period 1 July to 31 August 2011. So you need to make sure that you perform a query /auto filter on your extract along the lines of:

Ward admission date <= 31/08/2011 AND Ward discharge date > 30/06/2011

If you are in any doubt about how these greater than / equal to operands work, it sometimes helps to think about which records you want to exclude rather than which records you want to include. Hence in this example we are not interested in anyone who was admitted after 31st August because they can’t possibly have been occupying a bed in the hospital during the period we are interested in. In a similar vein, neither are we interested in including patients discharged before the end of June, because—again—they couldn’t possibly have been occupying a bed in the hospital during the two-month period.

Note that this extract has also been tweaked so that any patient that was still in hospital at the end of August has been assigned a TIME_OUT of 01/09/2011 00:01. And anyone who was admitted before the beginning of July has been assigned a TIME_IN of 01/07/2011 00:01.

Note also that the extract has been restricted to inpatient activity only. We haven’t bothered including day cases.

Step 2

Sort the inpatient ward stays into strict sequential-chronological order You need to ensure that you have a reliable patient identifier (CRN), and a marker that puts the admissions into sequential order (ADM_NO). It’s likely that there will be patients who experienced multiple admissions during the period of interest – you need to make sure that the first admission comes before the second admission, and so on. Essentially you want to sort your data in order of (a) patient (b) admission (c) ward stay. You should be able to do this reliably using the date of admission to each ward. So if you just order it according to (a) patient identifier, then (b) TIME_IN, you should be OK.

Step 3

Work out which wards “belong” to which specialties This is a potentially tricky step that might need a bit of background research, depending on how familiar you already are with how the wards in your hospital are organised. But basically you need to attribute a specialty to each ward so that you can then ascertain whether or not the specialty of the patient matches the specialty of the ward. When the specialty of the patient does not match the specialty of the ward, then you will have identified an outlier.

Although this sounds easy on paper, there are several things that make it more complicated in real life.

The first complication is that the way that wards are allocated to specialties “on the ground” can be quite different from the way that they appear on the Patient Management System (PMS). It may be that when you talk to the bed manager about boarding, he or she will tell you that they think of the hospital not in terms of the individual clinical specialties and sub-specialties identified on the PMS, but—insteadin terms of broader specialty groupings (like “Medical”, “Surgical”, “Head and Neck”, “Women and Children” etc.). Hence one of the things you will need to do to your list of specialties is assign a “broad specialty grouping” (we’ll call these BSGs from now on) to it. Hence Renal Medicine and Gastroenterology might get assigned to the BSG of “Medical”; Urology and Vascular Surgery might get assigned to the BSG of “Surgical”, and so on.

A second complication— and this one is potentially a show-stopper—is the complication of multi-specialty wards. If you have a ward—let’s call it Ward 8—that has a mix of Medical and Surgical beds, and there is no “dividing wall”—virtual or otherwise—between them, then this presents you with a real problem, because you have no way of knowing whether a medical patient is in a medical bed on the ward or on a surgical bed in the ward. One solution is that if you know that there are—say—six medical beds, every time there are more than six medical inpatients in Ward 8 then you’ll know that they are boarders. But this is a somewhat ungainly solution to a problem that is probably best dealt with by dividing the ward into two separate wards on the PMS. This problem—by the way—is what will prevent you from working out whether Renal patients are being accommodated in Renal beds or GI beds if you only have one ward that is a combination of both and nobody identifies which beds are which.

The other type of problematic multi-specialty ward is to be found in critical care areas like high dependency units (HDUs) or intensive therapy units (ITUs). It is often acceptable for either Medical or Surgical inpatients to be accommodated in these types of wards, so how can we identify when there is a mismatch. The answer is to identify outliers separately for each broad specialty grouping. This sounds like a pain but probably won’t be because it’s likely that the only type of outliers you’re interested in will be Medical outliers, so you’ll only ever really be doing this type of analysis with one BSG in mind.

So, from now on, let’s assume that you are doing this just so that you can count the numbers of Medical outliers in the hospital, in which case your next step will be to…

Step 4

Create two lookup tables The first lookup table will allocate each ward to a WARD_CLASS. The second lookup table will allocate each specialty to a BSG. In this example we will do these lookup tables purely from the point of view of the Medical BSG. In other words, we will allocate an ITU ward to “Medical” even though we know that it often accommodates Surgical and Orthopaedics patients. Anyway, if we can generate two lookup tables – one to put the wards into (WARD_CLASS) and one to put the specialties into (BSG – standing for “Broad Specialty Grouping”), then this will help us for the next step.

These two lookup tables are already in the Lookup Tables worksheet]

Step 5

Use the lookup tables to add boarding identifiers to the raw data table. You now need to use Excel’s =VLOOKUP function to allocate each ward to its relevant WARD_CLASS and each ward to its BSG.

This has already been done –in columns G and I.

Once you have done this, you can then create a column in your spreadsheet—in this example, it’s called OUTLIER—that contains an =IF statement to tell you if the WARD_CLASS matches the BSG.

This has already been done - in column J.

Step 6

There are two ways of counting outliers. The “traditional” method is to count the number of outlier bed days. So if John Smith, a medical inpatient, was in Ward 11 (a surgical ward) for three days in August, from the 8th to the 11th, this will count as three outlier bed days. By contrast, the other method says that John Smith was only moved once – on the 8th – so it’s the number of outlier events that we’ll count here rather than the beds occupied as a result of that event. Using this method, John Smith will count as one outlier event.

Both methods can be done using the same raw data file that we’ve been using here.

To count the number of outlier bed days, all you have to do is generate a pivot table where WARD is the row heading, OUTLIER is the column heading and Sum of WARD_LOS is the value field. This is the worksheet called PIVOT Outlier Bed Days

To count the number of outlier events, all you have to do is generate a pivot table where WARD is the row heading, OUTLIER is the column heading and Count of WARD_LOS is the value field. This is the worksheet called PIVOT Outlier Events.

[26 July 2013]


Make a comment on this blogpost:


Job Title:


Email address:


© Kurtosis 2013. All Rights Reserved.