1 Reply Latest reply on Nov 19, 2016 9:28 AM by Kevin Olsen

# Window Multiplier? Geometric Mean

I work in healthcare and we are trying to calculate a geometric mean length of stay by each department in the hospital. The data I am working with is at the patient-level, but we aggregate that data in order to get average length of stay by department. At this point, I need to calculate a geometric mean length of stay.

Normally, average length of stay would be calculated as follows for a group of 5 patients:

PatientLength of Stay
A5
B12
C2
D7
E8
Grand TotalAvg LOS: 6.8

However, the Geometric Mean Length of Stay is calculated by multiplying each individual patient length of stay by eachother and then taking the nth root of that (where n is the number of patients). The results for this would look as follows:

PatientLength of Stay
A5
B12
C2
D7
E8
Grand TotalGeometric Mean LOS:5.827

This result is from taking (5*12*2*7*8) ^(1/5)

I am wondering if anyone has a calculation that would multiply each patient's individual length of stay by the next patient's length of stay and aggregate that data at the department level?

Any help is GREATLY appreciated.

Thanks,
Luke

• ###### 1. Re: Window Multiplier? Geometric Mean

It can be done by converting raw patient level data to log values in the data source by using a query.  In tableau the arithmetic average of the log values will be the same as the geometric mean of the original values expressed in logs.  Once the aggregations are finished you convert the log values back to normal values in Tableau.

1. Create new field (log_LOS) in the data source by taking the base 10 log of the LOS with the patient level data

2. Create a calculated field in Tableau for GMLOS, define as (10 ^ Avg(log_LOS))

Here is the example with log value calculations in Excel. 1 of 1 people found this helpful