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.
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, 1-year, 3-year, 5-year, 10-year, etc.) and another for Standard Deviation of the month-to-month 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 month-to-month 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 (1-year, 3-year, 5-year 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 anyway--I was hoping Tableau could take some of the pain out of this process.
Thanks for your help.
Does that make more sense?
What exactly do you mean be " the various windows of time I'm looking at (1-year, 3-year, 5-year etc.)"?
To me those are ambiguous phrases. For example does 1-year mean:
- if today is 10/31/2011, 1-year=11/1/2010 to 10/31/2011
- if today is 10/31/2011, 1-year=10/1/2010 to 10/1/2011
- if today is 10/31/2011, 1-year=1/1/2010 to 12/31/2010
- last date in data is 6/30/2008, 1-year= 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?
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".
"As of Date" = 9/30/2008
"Year to Date" = 1/1/2008 - 9/30/2008
"1-Year" = 9/30/2007 - 9/30/2008
"2-Year" = 9/30/2006 - 9/30/2008
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 1-33) is just the crosstab export to Excel from the sample Tableau workbook I posted earlier. The middle section (rows 34-48) contains the calculations I need to create from the raw crosstab data. The bottom section (rows 50-71) contains the graphs that are driven by the calculations.
I hope this clarifies things somewhat. I very much appreciate any help anyone can provide!
sample_data_example_mock_up.xlsx 23.8 KB
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