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

    Window Multiplier? Geometric Mean

    Luke Mathews

      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
          Kevin Olsen

          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.