1 Reply Latest reply on Aug 14, 2018 7:07 AM by Joshua Milligan

    "Roll-up" data and recalculate result

    Christopher Ray

      Our datasets typically look something like this:

       

      Measure

      Doctor

      Numerator

      Denominator

      Result (Num/Denom*Multiplier)

      Multiplier

      1

      Jones

      1

      9

      1/9*100

      100

      2

      Jones

      3

      8

      3/8*100000

      100000

      3

      Jones

      6

      8

      6/8*100

      100

      1

      Ray

      22

      28

      22/28*100

      100

      2

      Ray

      88

      100

      88/100*100000

      100000

      3

      Ray

      12

      25

      12/25*100

      100

       

      For each measure, we would want to visualize a “rolled up” version of the measure’s performance. I.e., if I wanted to see how the entire enterprise is doing on Measure 1, we would combine Dr. Jones and Dr. Ray’s performance by adding both of their numerators and denominators and then recalculating the result by dividing the summed numerator and denominator.

       

      Example:

       

      Measure

      Doctor

      Numerator

      Denominator

      Result

      Multiplier

      1

      --

      1+22

      9+28

      23/50*100

      100

      2

      --

      3+88

      8+100

      91/108*100000

      100000

      3

      --

      6+12

      8+25

      14/37*100

      100

       

      I don’t know how to “roll-up” the data from it’s raw form and have the results recalculate. When adding the fields to a worksheet in Tableau, I only see options to sum the result, average, etc. I’ve been manually creating a separate data source of the rolled up data, but then it’s difficult to connect the various “levels” or the data since they’re all discrete sources.

        • 1. Re: "Roll-up" data and recalculate result
          Joshua Milligan

          Christopher,

           

          I'm guessing this is a Tableau Desktop question even though it somehow got categorized as a Tableau Prep question.  Assuming that, I would suggest creating a calculated field with code like this:

           

          SUM([Numerator]) / SUM([Denominator])

           

          As it uses an aggregation, the calculation is called an aggregate calculation and will be recomputed every time you change the level of detail in a view.  So, if you slice the data only by the Doctor, you'll get the results you described above.

           

          Hope that helps!

          Joshua