
1. Re: How to create a chart that displays correlation coefficient over time
Alex Kerin Oct 8, 2013 1:16 PM (in response to Patrick Lefler)1 of 1 people found this helpfulBefore you change the lookback period, is the result from the calc correct over the full time period  it looks like it should fluctuate? At the moment there is one value, presumably you want a rolling value?

2. Re: How to create a chart that displays correlation coefficient over time
Patrick Lefler Oct 8, 2013 1:24 PM (in response to Alex Kerin)Alex,
The Correlation Coefficient calc gives the correct value (.9258) for the entire time series (Aug2005  Aug 2013). I get the same result in Excel. Yes, I'm looking for a rolling value based on the user using the DIMENSION to determine the lookback period.
Thanks.

3. Re: How to create a chart that displays correlation coefficient over time
Alex Kerin Oct 8, 2013 4:48 PM (in response to Patrick Lefler)1 of 1 people found this helpfulOkay, I can get something by setting the window of the table calcs:
Deleted  see next
But changing the parameter only changes the scale, not the pattern. Do each of the windows I have set make sense  does each one look over the time period?

4. Re: How to create a chart that displays correlation coefficient over time
Alex Kerin Oct 8, 2013 4:52 PM (in response to Alex Kerin)1 of 1 people found this helpfulHang on, that should be:
(1/[Correlation Coefficient Lookback Period (Months)])*
WINDOW_SUM(
(SUM([X_VARIABLE])WINDOW_AVG(SUM([X_VARIABLE]),[Correlation Coefficient Lookback Period (Months)],0))/WINDOW_STDEV(SUM([X_VARIABLE]),[Correlation Coefficient Lookback Period (Months)],0))*
(SUM([Y_VARIABLE])WINDOW_AVG(SUM([Y_VARIABLE]),[Correlation Coefficient Lookback Period (Months)],0))/WINDOW_STDEV(SUM([Y_VARIABLE]),[Correlation Coefficient Lookback Period (Months)],0)
I still get values over 1 though

5. Re: How to create a chart that displays correlation coefficient over time
Shawn Wallwork Oct 8, 2013 6:02 PM (in response to Alex Kerin)Alex you're back from holiday! Good to see.
Shawn

6. Re: How to create a chart that displays correlation coefficient over time
Patrick Lefler Oct 8, 2013 7:12 PM (in response to Alex Kerin)Thanks Alex  at first glance, still looks like not quite there yet. Will take a deeper dive on your suggestion tomorrow. Very much appreciate your hard work and feedback.
Pat

7. Re: How to create a chart that displays correlation coefficient over time
Alex Kerin Oct 9, 2013 5:31 AM (in response to Patrick Lefler)1 of 1 people found this helpfulI would guess it's something to do with my interpretation of what needs to be in the window  especially the first divisor. Or perhaps you can't do this with the window approach. Could you share the Excel file with the correct formula?

8. Re: How to create a chart that displays correlation coefficient over time
Patrick Lefler Oct 9, 2013 7:40 AM (in response to Patrick Lefler)Alex,
See attached spreadsheet  CALC Tab. The yellow highlighted column contains somewhat complicated formula to allow user to dynamically change lookback period using Cell B1 as the input. Columns F through H contain simpler formulas for a particular (hardcoded) lookback periods.
I hope this helps. Again, very much appreciate your time and effort.
Pat

9. Re: How to create a chart that displays correlation coefficient over time
Alex Kerin Oct 9, 2013 7:47 AM (in response to Patrick Lefler)1 of 1 people found this helpfulThat doesn't help unfortunately as I don't know what the correl formula in excel is actually doing. The fact that you have recreated it correctly for the whole series is great, but I don't know enough to understand what then needs to be in the windows.

10. Re: How to create a chart that displays correlation coefficient over time
Patrick Lefler Oct 9, 2013 8:33 AM (in response to Patrick Lefler)1 of 1 people found this helpfulAlex,
I’ve taken the worksheet and tried to replicate what I’m looking to do using quick filters. My methodology is such that the lookback period starts 13 months prior and ends 1 month prior (12 months) not including the current month. I started with Aug2013 and worked backwards (each successive tab) to calculate the 12month Correlation Coefficient (CC). As I progress backwards, I get the following results using quick filters:
Aug2013 12month lookback period (Aug2012 to July 2013): CC = .9087
July2013 12month lookback period (Jul2012 to Jun2013): CC = .9379
Jun2013 12month lookback period (Jun2012 to May2013): CC = .8207
May2013 12month lookback period (May2012 to Apr2013): CC = .7915
Apr2013 12month lookback period (Apr2012 to Mar2013): CC = .8199
These are the exact results that the Excel CORREL function (attached spreadsheet) gives for the same 12month lookback periods.
I hope this helps.
I know I've said this before...your time and effort are very much appreciated on this.
Pat

11. Re: How to create a chart that displays correlation coefficient over time
Ramakrishnan R Feb 24, 2017 1:12 AM (in response to Patrick Lefler)Hi Patrick,
It is really promising for me as I have the same type of question. However the only challenge I face currently is.
I have huge list of items (namely "Equity Stocks") and their historical performance (each started at different time period and ways go back to more than 30 years sometime). I can manage the date filter and filter to choose the stock.
In your dashboard, you have only X variable and Y variable and the calculation is straight forward (variable is numeric value). But in my case, I have to choose the name of the stock (1 & 2 from 2 different filter) and their performance has to populate and i have to bring in to those in formula. How to bring that in the formula, because if i choose stock 1 and stock 2, the formula doesn't work because it is just the name of the stock.
Any help on this regard would certainly appreciate.