2 Replies Latest reply on Dec 11, 2018 2:49 PM by Joshua Milligan

    Aggregate using calculation (geometric mean or product) in Tableau Prep

    Rustin Reed

      I have a dataset that includes health samples collected from all US mines - they are consistent with environmental samples and tend to have log normal distribution. I need to aggregate samples by each mine site (anywhere from 0 to dozens of samples taken at each mine site) before joining this dataset with others.

       

      My Grouping dimension is the mine id’s, and the Aggregate measure is a concentration or dose. The structure looks something like this (not actual values):

       

      Mine ID, Percent of PEL

      100300, 12.5

      100300, 45.7

      100300,  121.8

      100301, 62.7

      etc

       

      Desired outcome

       

      Mine ID, Geomean of Percent of PEL

      100300, 41.1

      100301, 55.3

      100302, 21.8

      100303, 73.6

      etc

       

      Aggregating using the arithmetic mean would be inappropriate and, while median is an option, I would prefer to use the geometric mean.

       

      As far as I can tell, calculating a custom aggregate type during an Aggregate step is not possible. If I could even get the product of values for each mine id I could, in a separate step, create a calculated field using the product and nth root (from Count).

       

      Are there my options to do this without having to export to another program first, and then import back into Tableau Prep?

       

      Thank you in advance.

        • 1. Re: Aggregate using calculation (geometric mean or product) in Tableau Prep
          Jim Dehner

          Good morning

          Tableau Prep is design as tool to clean, reshape and connect data files rather than an analysis tool - if you could do the geometric mean in excel and then import the excel file you could then join it to your other files in Prep and complete the data cleansing process

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Aggregate using calculation (geometric mean or product) in Tableau Prep
            Joshua Milligan

            Rustin,

             

            This can be done in Tableau Prep!  It does require a little bit of calculation effort before and after the aggregation - but nothing too horrible.

             

             

            To get a geometric mean, you'll leverage Tableau's LN() and EXP() functions (natural log and exponent) in a flow similar to this:

             

             

            in the first Step, I'll create a calculation that simply returns the natural log of the Percent PEL field, like this,

             

             

            That's done per row, so I'll have to aggregate it per mine.  For a geometric mean, I'll average the natural log values, grouping by Mine ID:

             

             

            Finally, I'll use the EXP() function to get the final value for the geometric mean per mine:

             

            Other complex aggregations are also possible in Tableau Prep.

             

            On a related note:

            I don't entirely agree that Tableau Prep is only for data shaping and not for analysis - I think it is part of an ever growing stack (which includes Prep, Tableau Desktop, and Tableau Server) that start to blur the boundaries (in a good way) between data discovery, prep, analysis, and storytelling.  As Tableau Prep introduces features that have been previewed, such as native R and Python scripting and tighter integration with Tableau Server, I think we'll start to see it emerge as an indispensable tool that enables all kinds of complex analysis - as well as discovery and prep!

             

            Hope that helps!

            Joshua