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

    How can I compute a matrix of correlation coefficients?

    Jim Olness

      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.