7 Replies Latest reply on Jul 17, 2012 6:40 PM by Joe Mako

    Z Score Calculation - can anyone simplify?

    Emma Whyte

      Hello,

       

      Me and my colleague have been working on creating Z Score calculations (http://kb.tableausoftware.com/articles/knowledgebase/z-scores) for results of a satisfaction survey based on Teams of staff.

       

      We have written a number of calculations to create the Z Score calculated field, but this leaves our list of measures very long and very messy!

       

      I'm hoping that someone will be able to simplify our calculations for us. The issue we are having is that we keep getting an error whenever we try to combine all our calculated fields in to one: "is already an aggregation and cannot be further aggregated". So we have had to break the Z Score Calculation in to three parts.

       

      The calculations we have used are as follows:

      Team Mean Score

      Avg([Satisfaction Score])

       

      Mean of Population

      window_sum([Team Mean Score])/total(countd([Team]))

       

      Standard Deviation

      SQRT(

          window_sum(square([Team Mean Score]-[Mean of Population]))

          /

         total(countd([Team]))

      )

       

      Z Score

      (

      [Team Mean Score]

      -

      [Mean of Population]

      )

      /

      ([Standard Deviation])

       

      Please find attached a workbook showing these calculations in action to clarify our question.

       

      Can anyone simplify the calculations for us in to one calculated field?

       

      Thanks

       

      Emma

        • 1. Re: Z Score Calculation - can anyone simplify?
          Tracy Rodgers

          Hi Emma,

           

          If all you want is for the calculation to be one calculation versus three, then use the following:

           

          ((Avg([Satisfaction Score]))-(window_sum([Team Mean Score])/total(countd([Team])))) /

          SQRT(window_sum(square([Team Mean Score]-[Mean of Population])) /

          total(countd([Team])))

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Z Score Calculation - can anyone simplify?
            Joe Mako

            Here is a simplification of your Z Score formula:

             

            (

            AVG([Satisfaction Score])

            -

            WINDOW_AVG(AVG([Satisfaction Score]))

            )

            /

            WINDOW_STDEVP(AVG([Satisfaction Score]))

             

            The difference from average divided by the standard deviation of the population.

            • 3. Re: Z Score Calculation - can anyone simplify?
              Emma Whyte

              Hello,

               

              Thanks very much Tracy and Joe! I will be working on these calculations tomorrow and next week so will get back to you on the results.

               

              Thanks very much for the suggestions.

               

              Emma

              • 4. Re: Z Score Calculation - can anyone simplify?
                Emma Whyte

                Hello,

                 

                Joe - your calculation appears to work for questions where we are calculating Z Scores off a mean score - thanks!

                 

                The next step is to try and adapt this calculation for calculating Z Scores of a percentage, where we have to create the percentage first.

                 

                We record respondents answers to a numerical scale question in the database where answers can be 0-10.  We then calculate the percentage of those who scored 0 &1, and those who scored 9 & 10 (Net Promoter Score). I am trying to incorporate working out the percentages within the Z Score Calculation:

                 

                (

                (count([Promoters]) - count([Detractors]))

                /

                count([all question respondents])

                ) * 100

                 

                We have created 2 calculated fields (Promoters and Detractors) using IF statements.

                 

                Would anyone be able to adapt and integrate this calculation into a Z Score calculation to work out the AVG, Window_AVG and Window_StDevP?

                 

                Thanks very much,

                 

                Emma

                • 5. Re: Z Score Calculation - can anyone simplify?
                  Emma Whyte

                  Joe - would you be able to tell me what the difference is between WINDOW_STDEV and WINDOW_STDEVP?

                   

                  I have tried both in the formula you gave me above, and they produce very similar results. I understand STDEVP uses a biased population, but the Help topics are a bit tricky to understand on this function. Would you be able to make it any clearer?

                   

                  Thanks.

                   

                  Emma

                  • 6. Re: Z Score Calculation - can anyone simplify?
                    Joe Mako

                    As for the difference between stdev and stdevp, a quick Google search turned up:

                    Quote:

                    • STDEV is used when the group of numbers being evaluated are only a partial sampling of the whole population. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased."

                    • STDEVP is used when the group of numbers being evaluated is complete - it's the entire population of values. In this case, the 1 is NOT subtracted and the denominator for dividing the sum of squared deviations is simply N itself, the number of observations ( a count of items in the data set ). Technically, this is referred to as "biased." Remembering that the P in STDEVP stands for "population" may be helpful. Since the data set is not a mere sample, but constituted of ALL the actual values, this standard deviation function can return a more precise result.
                    • 7. Re: Z Score Calculation - can anyone simplify?
                      Joe Mako

                      It would be best if you could supply a sample packaged workbook, that represents your situation, and the numbers you expect as a final result from the sample data provided. Thank you.