8 Replies Latest reply on Aug 14, 2018 3:33 PM by Don Wise

    Need help with Standard Deviation calcuation

    Julie Nguyen

      Hello,

       

      My report is currently in Excel and Excel shows Standard Deviation calculation for data from 2016 to 2018 - the standard deviation calculation value is the same across all records from 2016 to 2018 .  (E.g., each record would have Standard Deviation column value of .14).

       

      In Tableau, I'm able to use the STDEVP function and I see the same value of .14, matching the Excel report.  But when I have Date in Columns, the standard deviation in Rows would show different value for each year.  The Date field would have the year 2016, 2017, and 2018.  Each year would show different standard deviation value.  How can I be able to use the Date that shows 2016, 2017, and 2018 but only have 1 value of standard deviation all across, not different value for each year?  I don't know if this make any sense to anyone or not.  Please help.

       

      Thank you.

        • 1. Re: Need help with Standard Deviation calcuation
          lei.chen.0

          Hello Julie,

           

          A general suggestion is to use LOD within STDEVP.

           

          For more detailed suggestions, please attache some sample data or a sample workbook.

           

           

          Regards

          Lei

          • 2. Re: Need help with Standard Deviation calcuation
            Julie Nguyen

            Hi Lei,

             

            I've attached a sample of the data.  Column C is formula.  I want to be able to do that in Tableau and when I create the chart, the chart would have the same standard deviation value of .14 for any given Date I choose to display in the Columns.  I couldn't get it to work because when I use Date to show 3 years (2016 to 2018) in the Columns, it would calculate standard deviation for each year which give different value.

             

            Thank you for your help.

             

            Warm Regards,

            Julie

            • 3. Re: Need help with Standard Deviation calcuation
              Don Wise

              Hi Julie,

               

              You can simply take the AVG of your existing Std. Deviation data to achieve the below by Year, by Quarter, by Month, etc.  Drag your Std. Deviation measure to the Marks Card and set it from SUM to AVG.

               

              Hope that helps!  Thx, Don

               

              Screen Shot 2018-08-14 at 1.25.21 PM.png

              Screen Shot 2018-08-14 at 1.25.25 PM.png

              Screen Shot 2018-08-14 at 1.25.29 PM.png

              • 4. Re: Need help with Standard Deviation calcuation
                Julie Nguyen

                Hi Don,

                 

                Thank you so much for the help.  The Sample I attached use Excel formula to get the Standard Deviation in column C.  I need to be able to calculate the standard deviation in Tableau, not relying on the data in Excel (column C Standard Deviation) because I need to develop the report in Tableau using the data in Excel given the only data to use are column A and B.  Thank you.

                 

                Warm Regards,

                Julie Nguyen

                • 5. Re: Need help with Standard Deviation calcuation
                  Don Wise

                  Hi Julie,

                  No problem please see new calculation (screenshot) below.  Only slightly different results than the aforementioned probably due to how Tableau calculates Std. Dev.  Thx, Don

                  Screen Shot 2018-08-14 at 1.45.43 PM.png

                   

                  Addendum, the values will be the same as the data type found, so if rolling up to Month/Year it will display correctly, but by Year no because your data is only at level of detail by Month/Year.  If that makes sense?  Also to match your prior values change calc to use: STDEVP.  I used STDEV.

                  • 6. Re: Need help with Standard Deviation calcuation
                    Julie Nguyen

                    Thank you very much Don.  It works.  But I don't understand why by year it doesn't display correct?

                     

                    Warm Regards,

                    Julie

                    • 7. Re: Need help with Standard Deviation calcuation
                      Don Wise

                      Hi Julie,

                       

                      I would think that it's due to the PREVIOUS_VALUE looking backwards against the set of data which when using YEAR is aggregated up to the year level from your month/year level of detail.  So, the calculation is only seeing two other values (YEARS) to compute on as opposed to several months (12) when drilled down.

                       

                      Essentially there's not enough information with the aggregated results to produce a calendar year result to match 0.14, unless you used MAX instead of average which would then bring you to 0.1380 for each year, with drill-down by Qtr, by Month, returning back to 0.1438 because there's an available lower level of detail there.  I would think that if there were a lower level of detail such as daily time for each day it might give you better results?

                       

                      There may be some other folks that might have some thoughts on this.  We'll see if they chime in!

                      • 8. Re: Need help with Standard Deviation calcuation
                        Don Wise

                        Hi Julie,

                        I gave it another go with a LOD calculation which will bring you the result you're looking for.

                        Screen Shot 2018-08-14 at 3.32.34 PM.png