5 Replies Latest reply on Nov 13, 2011 10:05 AM by Joe Mako

    Is there any workaround to getting the STDEV of Percent Difference?

    roger gill

      I am trying to find a way to get the standard deviation of monthly percent differences without having to go back to the data and manipulate it first.

       

      I obviously run into the "argument to stdev (an aggregate function) is already an aggregation, and cannot be further aggregated" issue. Can anyone think of a way around this?

       

      My end goal is to take stock market pricing data and look at annualized returns over 1,3,5,10, etc. year periods as well as the corresponding standard deviation of the monthly changes over similar time periods. The attached sample workbook has the returns working more or less as expected but I'm stumped on the standard deviation part.

        • 1. Re: Is there any workaround to getting the STDEV of Percent Difference?
          Joe Mako

          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

            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.

             

            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

              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?

              • 4. Re: Is there any workaround to getting the STDEV of Percent Difference?
                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

                "1-Year" = 9/30/2007 - 9/30/2008

                "2-Year" = 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 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!

                 

                Roger

                • 5. Re: Is there any workaround to getting the STDEV of Percent Difference?
                  Joe Mako

                  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.