13 Replies Latest reply on Feb 1, 2018 8:10 AM by nyi.htoon

    Matrix Calculation

    George Nguyen

      I want to take the values of the column and divide it by the row.

       

      What's the best way to handle this?

      Sample_CorrelationTable_v2.PNG.png

        • 2. Re: Re: Matrix Calculation
          George Nguyen

          Looks like I spoke too soon. When I went back in to calculate the correlation coefficient, the data would not display.

           

          I tried two different ways to calculate:

           

          Method 1:

          (WINDOW_SUM(SIZE()*[ValueAcross]*[ValueDown])-WINDOW_SUM([ValueDown])*WINDOW_SUM([ValueAcross]))

          /

          (SQRT(((WINDOW_SUM(SIZE()*[ValueAcross]^2)-WINDOW_SUM([ValueAcross])^2))*(WINDOW_SUM(SIZE()*[ValueDown]^2)-WINDOW_SUM([ValueDown])^2)))

           

          Method 2:

          (

          1/(LAST()-FIRST()))*

          WINDOW_SUM((([ValueDown])-WINDOW_AVG(([ValueDown])))

          /

          WINDOW_STDEV(([ValueDown]))*(([ValueAcross])-WINDOW_AVG(([ValueAcross])))/WINDOW_STDEV(([ValueAcross]))

          )

           

          Jonathan Drummey, any help?

           

           

          Thanks!

          • 3. Re: Re: Matrix Calculation
            George Nguyen

            Bora Beran, your visualization is really cool - and extremely useful!

             

            https://boraberan.wordpress.com/2013/12/09/creating-a-correlation-matrix-in-tableau-using-r-or-table-calculations/

             

            I'm trying to replicate it as well using table calculations, but seem to can't get the correlation coefficient calculation to work. Any feedback?

             

             

            Thanks!

            • 4. Re: Re: Re: Matrix Calculation
              George Nguyen

              Here is the formula I used:

              CorrelationCoefficient_Formula.PNG

               

              Instead of writing all this in one Calculated Field, I broke it up into parts so that I can check my math and note any issues I see along the way.

               

              Numerator: Part 1

              [Count]*(WINDOW_SUM(([ValueDown]*[ValueAcross])))

               

              Numerator: Part 2

              WINDOW_SUM([ValueDown])*WINDOW_SUM([ValueAcross])

               

              Denominator: Part 1

              ([Count]*WINDOW_SUM([ValueDown])^2)-(WINDOW_SUM([ValueDown])^2)

               

              Denominator: Part 2

              ([Count]*WINDOW_SUM([ValueAcross])^2)-(WINDOW_SUM([ValueAcross])^2)

               

              Numerator Calculation:

              [Numerator: P1]-[Numerator: P2]

               

              Denominator Calculation:

              SQRT([Denominator: Part1]*[Denominator: Part2])

               

              Here are the numbers:

              CorrelationCoefficient_Calculations.PNG

               

              I noticed that the Numerator Calc equals 0 because Numerator Part 1 and Numerator Part 2 are the same.

               

              I've been stuck on this problem all day. Please help - anyone!

              • 5. Re: Re: Re: Re: Matrix Calculation
                Bora Beran

                Hi George,

                Is this what you're looking for?

                 

                Correlationexample.JPG.jpg

                 

                ~ Bora

                • 6. Re: Matrix Calculation
                  George Nguyen

                  Hi Bora Beran,

                   

                  Yes, this is what I want.

                   

                  However, when I tried to duplicate, I get as far as creating the Correlation Coefficient calculated field, but when I drop this calculated field into the table, it displays nothing.

                   

                  Here is my process:

                  1. Indices pill to Row Shelf and Indices (copy) pill to Columns Shelf
                  2. Values pill to Marks Shelf - displayed as Text, then add Quick Calculation for Running Total
                  3. Create ValueAcross calculated field with formula: PREVIOUS_VALUE(WINDOW_MAX(SUM([Value])))
                  4. Create ValueDown calculated field with formula: PREVIOUS_VALUE(WINDOW_MAX(SUM([Value])))
                  5. Drop ValueAcross pill into Measure Values Shelf - computer using Table (Across)
                  6. Drop ValueDown pill into Measure Values Shelf - computer using Table (Down)
                  7. Create Correlation Coefficient calculated field with formula:
                    (WINDOW_SUM(SIZE()*[ValueAcross]*[ValueDown])-WINDOW_SUM([ValueDown])*WINDOW_SUM([ValueAcross]))
                    /
                    (SQRT(((WINDOW_SUM(SIZE()*[ValueAcross]^2)-WINDOW_SUM([ValueAcross])^2))*(WINDOW_SUM(SIZE()*[ValueDown]^2)-WINDOW_SUM([ValueDown])^2)))
                  8. Drop Correlation Coefficient pill into Measure Values Shelf

                   

                  I get this table display.

                  CorrelationCoefficient_Display.PNG

                  When I try to replicate your table display based on your arrangements of pills on shelves, it displays nothing.

                   

                  Would it be too much to ask you to upload a video of how you constructed your table display?

                   

                   

                  Thanks,

                  George

                  • 7. Re: Matrix Calculation
                    Bora Beran

                    Hi George,

                    Can you check if the table calc settings are correct? Addressing and partitioning settings make a big difference. I see Table Across in your screenshot which is probably the reason why you are getting those blank rows.

                     

                    If you go to Advanced Settings as shown below and move Date field to addressing it should give you the results you're looking for.

                    tablecalcsettings.jpg

                     

                    ~ Bora

                    • 8. Re: Re: Matrix Calculation
                      George Nguyen

                      I tried that as well, but it only returned the value 1.000 for all cells. See below:

                      CorrelationCoefficient_AdvancedSettings.PNG


                      I have also attached the workbook so you can see what I did.

                      • 9. Re: Re: Matrix Calculation
                        Bora Beran

                        tablecalcs.jpg

                        1 of 1 people found this helpful
                        • 10. Re: Re: Matrix Calculation
                          George Nguyen

                          Brilliant!!! Gotta love the intricacies of Tableau.

                           

                          Thank you so much.

                          • 11. Re: Re: Matrix Calculation
                            Bora Beran

                            Please, keep in mind, it will happen at the level of detail you use with Date field. E.g. if it is YEAR(Date) it will calculate the annual SUM(Value) then calculate correlation based on that. If you set it to month, it would be monthly. So if you want to calculate based on daily data you should set it to DAY(Date).

                            • 12. Re: Re: Re: Re: Matrix Calculation
                              Santiago Calvo

                              Hi Bora,

                               

                              I´m trying to learn how to do a correlation analisis in Tableau, I´ve seen your answers to George´s question and it has been really usefull to me.

                              I downloaded the Tableau correlation you shared and I tried to duplicate it, I could do it, but now I would like to use the correlation analisis with my database and I can´t... My problem is how to structure my database.

                              Could you please share a database that allows correlation analisis or at least explain how should the data be display to permit this kind of analisis.

                               

                              Thank you very much,

                                
                              Santiago

                              • 13. Re: Matrix Calculation
                                nyi.htoon

                                Hi Bora, I was going through your correlation coefficient example using market indexes. The downloaded tableau workbook did not include underlying data. Can you please share it? Thanks.

                                 

                                I also have another question; if a matrix is given (not computed), how would tableau handle it? I have a scenario where a matrix is given, but some calculations are based on it. Thanks again.