5 Replies Latest reply on Dec 10, 2013 1:42 AM by colum.mccoole

# How can I compute a matrix of correlation coefficients?

I have a raw data set consisting of three columns: Date, Metric, and Value. To be concrete, let's assume that there are three different values in the Metric column, i.e., three different Metrics. I want to compute the 3x3 matrix of correlation coefficients for these Metrics. I would happily settle for one row or one column of the matrix a time.

I created an array of time-series charts, with Date as a column label and Metric as a row label. Then I created the following custom field:

1/(1 - SIZE())

*

WINDOW_SUM(

( SUM([Value]) - WINDOW_AVG(SUM([Value])) )  WINDOW_STDEV(SUM([Value]))

*

( LOOKUP(SUM([Value])) - WINDOW_AVG(LOOKUP(SUM([Value]))) )  WINDOW_STDEV(LOOKUP(SUM([Value])))

)

I set Date as the addressing field and Metric as the partitioning field. I had hoped to use the Lookup function to designate a particular Metric as the 'base' case for purposes of computing correlation coefficients. Unfortunately, Lookup is searching across members of the addressing field, not across members of the partitioning field. I need a way to search across members of the partitioning field.

Actually, I just need a way to compute a large number of correlation coefficients quickly, for data sets that are structured like the one in this example. I've read quite a number of help documents and forum postings but have come up empty thus far.

My Tableau desktop is out of commission at the moment. I'll attach my example when I get it working again. Meanwhile, I'd appreciate any suggestions.

• ###### 1. Re: How can I compute a matrix of correlation coefficients?

(twbx example file attached)

• ###### 2. Re: How can I compute a matrix of correlation coefficients?

Jim. Did you ever get a satisfactory solution to your problem?  It's partly related to something I was trying to do here: http://community.tableau.com/thread/129350

So I thought I would ask.

Thanks,

Colum

• ###### 3. Re: How can I compute a matrix of correlation coefficients?

I also would be interested in this solution - just cross correlation matrix between N time series. Does not need to be rolling although thats a plus.

• ###### 4. Re: How can I compute a matrix of correlation coefficients?

I have an example on my blog which should do what you are asking for

Creating a correlation matrix in Tableau using R or Table Calculations

In this case, for each item along the axis, the series of values consists of different variables, instead of those in your case there would be timestamps.  Using the correct addressing and partitioning is the key in getting the expected results.

• ###### 5. Re: How can I compute a matrix of correlation coefficients?

Thank you Bora.  I will take a look.