3 Replies Latest reply on Feb 4, 2014 5:50 AM by Duncan Slater

# Dynamic Correlations...

Hello, I am having great fun with correlations in Tableau. I can view the correlation coefficient on my charts thanks to Joe Mako's

I would like to take it a step further and show a heat map of the correlation coefficients between the various variables, calculated dynamically i.e. if I bring in a dimension onto the heat map then the individual measure correlations would show by that dimension.

I guess my problem is: how do I show the output of the Correlation calculation without the accompanying data...

Anybody done this before? Do I need to use R or will a table calculation do the job?

Please see attached example where I have typed the output of the tableau correlation calculation into a further excel sheet to get the output I am after....

Many Thanks

Duncan

p.s. I have seen Bora Beran's post with the Car Correlations but I guess I can't translate the addressing and partitioning to my example

• ###### 1. Re: Dynamic Correlations...

Hi Duncan,

Does this blog post by Jonathon Drummey help? Comparing Each Against Each Other: The No-SQL Cross Product | Drawing with Numbers

1 of 1 people found this helpful
• ###### 2. Re: Dynamic Correlations...

Hi again Duncan,

I think you need to reshape the data in order for this to work, so that measure names is one column,and measure values is another column. You can do this with a union in custom SQL:

SELECT [Input\$].[A] AS [Measure_Value],

[Input\$].[GFCID] AS [GFCID],

[Input\$].[Region] AS [Region],

'A' AS [Measure_Name]

FROM [Input\$]

UNION

SELECT [Input\$].[B] AS [Measure_Value],

[Input\$].[GFCID] AS [GFCID],

[Input\$].[Region] AS [Region],

'B' AS [Measure_Name]

FROM [Input\$]

UNION

SELECT [Input\$].[C] AS [Measure_Value],

[Input\$].[GFCID] AS [GFCID],

[Input\$].[Region] AS [Region],

'C' AS [Measure_Name]

FROM [Input\$]

UNION

SELECT [Input\$].[D] AS [Measure_Value],

[Input\$].[GFCID] AS [GFCID],

[Input\$].[Region] AS [Region],

'D' AS [Measure_Name]

FROM [Input\$]

Then you can use data blending between this data source and the original data source. Create a parameter that has values A, B, C and D, and then create a calculated field in the original source that returns the selected measure.

In the secondary data source, use a filter  so that you will never compare a measure to itself:

parameter <> Measure_Name

Also with this method you have the correlation not only between A and the other measures, but actually you can compare all measures to all other measures.

I don't really understand the formula for correlation, so I included one with the difference just to illustrate the point. I tried using the correlation formula you had, and I think there may be some issues with nesting, where one of the inner fomulas needs to be computed along a different dimension than the outer window sum.

1 of 1 people found this helpful
• ###### 3. Re: Dynamic Correlations...

Thanks Bethany, I will explore these options and see if I can crack it. I had already started re-shaping the data to get it closer to the car example! Will let you know how I get on.... Cheers Duncan