4 Replies Latest reply on Jul 16, 2015 7:03 AM by mark.jurriesii

    Standard Deviation in Calculated Field Using Dimension

    mark.jurriesii

      Hello,

       

      I have a set of monthly data that I'd like to aggregate and calculate z-scores on, which I'd use in a table to show the z-scores for the last month. I can get calculate the z-score on a trendline easily, but I'd like to sort or filter on the last month's z-score only, hence the need for a calculated field. The data is set up in a way that there are multiple rows per month, i.e.:

       

      MonthCategoryCount
      10/1/2013A1000
      10/1/2013B1500
      11/1/2013A1100
      11/1/2013B1450
      12/1/2013A1200
      12/1/2013B1600

       

      So far, I've got the easy pieces done:

       

      Last Month: sum(if month="x" then [count] end)

      Average: sum([Count])/CountD([Month])

       

      I'm running into trouble with the standard deviation, though. If I use the STDEV function in a calculated field, I get STDEV(1000,1500,1100,1450,1200,1600)=242, which is of course how it's supposed to work. What I'd like to get instead is the monthly aggregation, that is STDEV((1000+1500),(1100+1450),(1200+1600)), or STDEV(2500,2550,2800)=161. I'm sure if I created a calculated field for each month I could get it to work, but I'm wondering if there's a simpler way that I'm perhaps missing.

        • 1. Re: Standard Deviation in Calculated Field Using Dimension
          Ramon Martinez

          Hello Mark,

           

          To solve your scenario and requirements we have to use Table Calculation for calculating Mean, Standard Deviation and Z-score based on aggregated data per month.

           

          Table calculations (Mean, Stdev and Z-score) should be compute using Table down in the configuration of the worksheet Sheet 2.

           

          See attached a workbook using your sample data, specifically Sheet 2

           

          Note: Attached workbook is in version 8.0

           

          I hope this helps,

           

          Best regards,

          Ramon

          1 of 1 people found this helpful
          • 2. Re: Standard Deviation in Calculated Field Using Dimension
            mark.jurriesii

            Thanks for the help, Ramon. I was looking to create a calc that would show the standard deviation without showing the months. Since I'm using a 13-month history, I was able to create a calculated field that will be fairly easy to change depending on the timeframe I'm looking at. Appreciate the assist, the support for this product is fantastic.

            • 3. Re: Standard Deviation in Calculated Field Using Dimension
              Victor Mora

              Hello:

              I have a table with 500 part numbers and 24 months of sales history. I used the STDEV(Sales) in table to calculate a 24-months standard deviation and compared against what excel calculates and the numbers are way-off. (Average are exactly the same). How is the Std Dev calculated in Tableau, is it different than Excel?

               

              Thanks.

               

                 

              Stock CodeExcelTableau
              Item A37069
              Item B55191
              Item C42036
              Item D22222
              Item E68747
              Item F1,07967
              Item G2,535263
              Item H2,232208
              • 4. Re: Standard Deviation in Calculated Field Using Dimension
                mark.jurriesii

                Hard to say without knowing how your data is aggregated. You're probably best off using WINDOW_STDEV(metric). This will calculate the standard deviation at the level the window displays. If you're looking at a sum, (i.e. Nov. is part 1 + part 2 + part 3), the stdev will look at each unit instead of the whole thing. But if it's summed in the window, then WINDOW_STDEV will work from there.

                 

                You can create the WINDOW_STDEV as a calculated field, then reference it in another field (i.e. WINDOW_AVG) to derive your zscore.