4 Replies Latest reply on May 18, 2016 8:38 AM by G Marc Turner

    How to calculate the standard deviation?

    jeff canmils

      Hi

       

      I'm trying to calculate the standard deviation aggregated by country and dealer, but so far I haven't been successful. The idea is to calculate basic statistics like sum, count, average and std deviation.

       

      I did try with the following code:

       

      { INCLUDE  [Country Repaired], [Dealer Name]:

      AVG(zn([Number of Records Flagged]))

      }

       

      { INCLUDE  [Country Repaired], [Dealer Name]:

      STDEV(zn([Number of Records Flagged]))

      }

       

      but did not work

       

      I have attached the tableau file. I'm using version 9.3.

       

      Please, let me know if you need more information, any help will be really appreciate it.

       

      Thanks.

       

      Message was edited by: joan casellas

       

      Message was edited by: joan casellas I just added the tableau file with the sample data

        • 1. Re: How to calculate the standard deviation?
          John Sobczak

          Have you tried using the out-of-the-box reference line standard deviation?  Of course you can further define the level of detail in the view.

           

          Standard Deviation Reference Line.png

          • 2. Re: How to calculate the standard deviation?
            G Marc Turner

            Hi Joan,

            I'm unsure of what you mean by it not working. The workbook you attached does not contain the sample data excel file so I can't see the results you are getting. If you export the workbook as a packaged workbook it should include the data file.

             

            There are two things that I see which might be happening though if you are comparing excel to Tableau and getting different results. If you are using the population standard deviation in one and the sample standard deviation in the other, the results would be different. Second, the zn() tells Tableau to replace null values with zero. Unless you have something similar in Excel, then excel will be ignoring the null values but Tableau will count them as zeros. This could lead to very different results depending on the data and the number of missing values.

             

            Hope this helps

            -Marc

            • 3. Re: How to calculate the standard deviation?
              jeff canmils

              Hi Marc,

               

              Thanks for your answer.

               

              what I  mean when is not working is that the result I'm getting is 0. Yes, zn tells Tableau to replace null values with zero, which is not what I want (I just realize about that). I would like to get some basic statistics like average and std deviation ignoring the null values. I would like to assume the whole population.

               

              I have attached the file as packaged workbook.

              • 4. Re: How to calculate the standard deviation?
                G Marc Turner

                Hi Joan,

                In your data it appears that you only have one record for each dealer. So, when you are calculating the standard deviation at the dealer level there is no variance since there is only a single value. This is also impacting the average. If you remove [Dealer Name] from the formulas and the zn() you should get results that match what is in the Excel file. I did notice though that in Excel it appears to be calculating the population Standard Deviation rather than the sample, so in Tableau it would be STDEVP.

                Hope this helps

                -Marc

                1 of 1 people found this helpful