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,
StandardDev.twbx 10.6 KB
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.
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?
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
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.