11 Replies Latest reply on Feb 24, 2017 1:12 AM by Ramakrishnan R

# How to create a chart that displays correlation coefficient over time

I am trying to create a chart to display the historic trend of correlation coefficient based on a user defined lookback period (which I defined as the DIMENSION “Correlation Coefficient Lookback Period”).

I have the correlation coefficient calculation, but need assistance on how to get to the next step of creating a line chart that is dynamic based on the user selecting the lookback period used to calculated the coefficient.

I’ve attached both the packaged Tableau Worksheet along with an Excel spreadsheet (CALC Tab) that shows how it was done in Excel.

The end result is a Tableau chart that looks similar to this (based, for example, on an 18 month lookback period)

Thank you in advance for your time, thoughts and feedback. Much appreciated!!

Pat Lefler

• ###### 1. Re: How to create a chart that displays correlation coefficient over time

Before 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?

1 of 1 people found this helpful
• ###### 2. Re: How to create a chart that displays correlation coefficient over time

Alex,

The Correlation Coefficient calc gives the correct value (.9258) for the entire time series (Aug-2005 - 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

Okay, 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?

1 of 1 people found this helpful
• ###### 4. Re: How to create a chart that displays correlation coefficient over time

Hang 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

1 of 1 people found this helpful
• ###### 5. Re: How to create a chart that displays correlation coefficient over time

Alex you're back from holiday! Good to see.

--Shawn

• ###### 6. Re: How to create a chart that displays correlation coefficient over time

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

I 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?

1 of 1 people found this helpful
• ###### 8. Re: How to create a chart that displays correlation coefficient over time

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 (hard-coded) 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

That 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.

1 of 1 people found this helpful
• ###### 10. Re: How to create a chart that displays correlation coefficient over time

Alex,

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 Aug-2013 and worked backwards (each successive tab) to calculate the 12-month Correlation Coefficient (CC). As I progress backwards, I get the following results using quick filters:

Aug-2013 12-month lookback period (Aug-2012 to July 2013): CC = .9087

July-2013 12-month lookback period (Jul-2012 to Jun-2013): CC = .9379

Jun-2013 12-month lookback period (Jun-2012 to May-2013): CC = .8207

May-2013 12-month lookback period (May-2012 to Apr-2013): CC = .7915

Apr-2013 12-month lookback period (Apr-2012 to Mar-2013): CC = .8199

These are the exact results that the Excel CORREL function (attached spreadsheet) gives for the same 12-month lookback periods.

I hope this helps.

I know I've said this before...your time and effort are very much appreciated on this.

Pat

1 of 1 people found this helpful
• ###### 11. Re: How to create a chart that displays correlation coefficient over time

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.