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

# Standard Deviation in Calculated Field Using Dimension

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.:

 Month Category Count 10/1/2013 A 1000 10/1/2013 B 1500 11/1/2013 A 1100 11/1/2013 B 1450 12/1/2013 A 1200 12/1/2013 B 1600

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

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

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

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 Code Excel Tableau Item A 370 69 Item B 551 91 Item C 420 36 Item D 222 22 Item E 687 47 Item F 1,079 67 Item G 2,535 263 Item H 2,232 208
• ###### 4. Re: Standard Deviation in Calculated Field Using Dimension

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.