
1. Re: Need help with Standard Deviation calcuation
lei.chen.0 Aug 13, 2018 7:15 PM (in response to Julie Nguyen)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 Aug 14, 2018 1:11 PM (in response to lei.chen.0)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

Sample.xlsx 9.5 KB


3. Re: Need help with Standard Deviation calcuation
Don Wise Aug 14, 2018 1:27 PM (in response to Julie Nguyen) 
4. Re: Need help with Standard Deviation calcuation
Julie Nguyen Aug 14, 2018 1:39 PM (in response to Don Wise)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 Aug 14, 2018 2:20 PM (in response to Julie Nguyen)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
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 Aug 14, 2018 2:47 PM (in response to Don Wise)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 Aug 14, 2018 3:07 PM (in response to Julie Nguyen)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 drilldown 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 Aug 14, 2018 3:33 PM (in response to Julie Nguyen)