
1. Re: Is there any workaround to getting the STDEV of Percent Difference?
Joe Mako Oct 29, 2011 10:40 PM (in response to roger gill)Sounds like you are looking for an aggregate of an aggregate. There are a few ways to accomplish this, but I am not quite sure I understand exactly what you want to do Can you mock up what you are looking for in a final result?
You could add a reference line, that will aggregate the marks displayed, or you can use a custom table calculation with the WINDOW_STDEV() function.
With more details on exactly what you would like to accomplish, details can be provided.

2. Re: Is there any workaround to getting the STDEV of Percent Difference?
roger gill Oct 31, 2011 11:16 AM (in response to roger gill)Given data that is structured like:
Date..... Symbol. Price  01/31/2006 AAPL 75.51 02/28/2006 AAPL 68.54 03/31/2006 AAPL 62.72 04/30/2006 AAPL 70.39 05/31/2006 AAPL 59.77 06/30/2006 AAPL 57.12 07/31/2006 AAPL 67.96 08/31/2006 AAPL 67.85
I would like to derive two sets of graphs. One for Annualized Return over various intervals (YTD, 1year, 3year, 5year, 10year, etc.) and another for Standard Deviation of the monthtomonth price changes over similar intervals.
I have the Annualized Returns part working but I am having trouble with the Standard Deviations.
Using table calculations
(ZN(SUM([Closing Price]))  LOOKUP(ZN(SUM([Closing Price])), 1)) / ABS(LOOKUP(ZN(SUM([Closing Price])), 1))
I can compute the monthtomonth changes in price:
Date..... Symbol. Price. %Diff  01/31/2006 AAPL 75.51 02/28/2006 AAPL 68.54 9.23 03/31/2006 AAPL 62.72 8.49 04/30/2006 AAPL 70.39 12.23 05/31/2006 AAPL 59.77 15.09 06/30/2006 AAPL 57.12 4.43 07/31/2006 AAPL 67.96 18.98 08/31/2006 AAPL 67.85 0.16
But I can't figure out a way to take the standard deviation of those monthly price differences over the various windows of time I'm looking at (1year, 3year, 5year etc.) since any calculation I come up with results in the "cannot be further aggregated" error.
In the end, I'm looking to get one stdev number for each time period for each symbol I'm looking at but I can't find a way to do anything with the calculated "Percent Difference" values other than display them. There doesn't seem to be a way to manipulate the data further. Left to my own devices, I might try either custom SQL or to export it out of SQL and format the data in some other application first, but that is what we've been doing to date anywayI was hoping Tableau could take some of the pain out of this process.
Any ideas?
Thanks for your help.
Does that make more sense?

3. Re: Is there any workaround to getting the STDEV of Percent Difference?
Joe Mako Oct 31, 2011 12:01 PM (in response to roger gill)What exactly do you mean be " the various windows of time I'm looking at (1year, 3year, 5year etc.)"?
To me those are ambiguous phrases. For example does 1year mean:
 if today is 10/31/2011, 1year=11/1/2010 to 10/31/2011
 if today is 10/31/2011, 1year=10/1/2010 to 10/1/2011
 if today is 10/31/2011, 1year=1/1/2010 to 12/31/2010
 last date in data is 6/30/2008, 1year= 7/23/2007 to 6/30/2008
 etc with many other interpretation.
from the sample data that you provided, what would you expect for the final result? can you mock something up?

4. Re: Is there any workaround to getting the STDEV of Percent Difference?
roger gill Nov 8, 2011 2:39 PM (in response to roger gill)Sorry for the confusion!
I have a parameter in Tableau called "As of Date" and the time periods I'm using are in reference to the selected "As of Date".
e.g.:
"As of Date" = 9/30/2008
"Year to Date" = 1/1/2008  9/30/2008
"1Year" = 9/30/2007  9/30/2008
"2Year" = 9/30/2006  9/30/2008
etc.
The solution I came up with for the Annualized Returns part works already, since I just need two data points (the stock price at the As of Date and the stock price at an earlier point in time  1 year, 2 years, etc.). The Standard Deviation part has proven trickier for me since I need to take the STDEV of a range of datapoints that only exist as table calculations in Tableau.
Attached is a mockup in Excel of what I'm trying to achieve. The top part of the worksheet (rows 133) is just the crosstab export to Excel from the sample Tableau workbook I posted earlier. The middle section (rows 3448) contains the calculations I need to create from the raw crosstab data. The bottom section (rows 5071) contains the graphs that are driven by the calculations.
I hope this clarifies things somewhat. I very much appreciate any help anyone can provide!
Roger

sample_data_example_mock_up.xlsx 23.8 KB


5. Re: Is there any workaround to getting the STDEV of Percent Difference?
Joe Mako Nov 13, 2011 10:05 AM (in response to roger gill)RG,
Thank you for the additional details, there are numerous ways to accomplish this, and what attached may not fit if there are additional constraints or situations, such as, but not limited to, if each symbol has a different inception date.
For the Return calculations I just used aggregations, while for the standard deviation, table calculations were necessary. There is all sorts of other logic and business rules, or data situations that could also be handled for with additional formula logic. The attached simple recreates your Excel charts.

sampledata_jm_edit.twbx 86.6 KB
