5 Replies Latest reply on Jan 18, 2019 10:54 AM by Glenn Kuly

    R Integration--pass fixed argument to R--trim value for mean

    Glenn Kuly

      I'm new to using R with Tableau, and not finding a solution for my trimmed mean issue elsewhere, please excuse the newbie question.

       

      I have a simple dataset of response values with outliers, and mean(mydata$Response, trim = .05) returns the correct trimmed mean value in R. I've tried setting up the equivalent calculation in Tableau:

       

                     Trimmed Mean:

      SCRIPT_REAL("mean(.arg1, trim = 0.05)",

      AVG([Response])

      )

       

      But what i'm getting back is the full (untrimmed) mean value. I've also tried passing the trim argument as a vector, and that doesn't work either.

       

      How can i correct the expression?

       

      -- Glenn

        • 1. Re: R Integration--pass fixed argument to R--trim value for mean
          Glenn Kuly

          Updating my thread, Trimmed Mean: SCRIPT_REAL("mean(.arg1, trim = 0.05)", AVG([Response])) does actually work, as long as response ID number in the view (which of course shows all rows for Response).

           

           

          Trying to figure out a way of using an LOD expression (include) for arg1 so that i can show the average alone (without all individual values in the view).  So far no success.

           

          -- Glenn

          • 2. Re: R Integration--pass fixed argument to R--trim value for mean
            Jim Dehner

            Hi Glenn

            the interface with R and with Python works through table calculations so you would have to do it like the attached

            first I calculated the fixed avg by state

            then the single fixed overall value

            then dropped it into a lookup function to make it into a table calc

             

            then went out to R

             

            to get this

             

             

            its the long way around but maybe it will meet your need

             

            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.

            • 3. Re: R Integration--pass fixed argument to R--trim value for mean
              Glenn Kuly

              Hi Jim, does that series of calculations produce the trimmed mean (w/ top and bottom 5% of observations removed)?

               

              Comparing R Trim vs AVG([Sales]) shows identical results (column 1 is total using average) :

              • 4. Re: R Integration--pass fixed argument to R--trim value for mean
                Jim Dehner

                I don't know - that wasn't the question you asked

                 

                 

                You have to work from a table calculation - table calculations are at the bottom of the Order of operation so any LOD is executed above the table calculations

                also any top / bottom 10 will depend on a table calculation - see below it seems to calculate the avg of the 10 _ I don't think that is what you meant

                 

                 

                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.

                • 5. Re: R Integration--pass fixed argument to R--trim value for mean
                  Glenn Kuly

                  Apologies for the misunderstanding, I'm looking to use the mean function in R, with an argument for trim to eliminate the highest and lowest X% of values in a dataset where there are extreme outliers.

                   

                  So if i use the following calculated field in your workbook for trimmed mean (5%) of average sales,

                   

                                R Timmed 2:

                  SCRIPT_REAL(

                  "mean(.arg1, trim = 0.05)",

                  AVG([Sales])

                  )

                   

                  the result is $242:

                   

                   

                  The problem is trying to show the total only (without rows for states).

                   

                  I know that there is a technique (developed here http://drawingwithnumbers.artisart.org/trimmean-in-tableau/) for calculating trimmed mean within Tableau using Rank_Unique, but it won't work where the data is highly skewed.

                   

                  -- Glenn