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

    Dynamic Correlations...

    Duncan Slater

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

      post back in 2011. http://community.tableau.com/thread/111548

       

      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...
          Bethany Lyons

          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...
            Bethany Lyons

            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...
              Duncan Slater

              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