
1. Re: Matrix Calculation
George Nguyen Jan 29, 2014 2:38 PM (in response to George Nguyen)Found the solution after reading this post:
Comparing Each Against Each Other: The NoSQL Cross Product  Drawing with Numbers
Again, thank you Jonathan Drummey.

2. Re: Re: Matrix Calculation
George Nguyen Jan 31, 2014 3:12 PM (in response to 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!

CorrelationCoefficient.twbx 40.8 KB


3. Re: Re: Matrix Calculation
George Nguyen Jan 31, 2014 4:09 PM (in response to George Nguyen)Bora Beran, your visualization is really cool  and extremely useful!
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 Jan 31, 2014 5:19 PM (in response to George Nguyen)Here is the formula I used:
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:
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!

CorrelationCoefficient.twbx 45.5 KB


5. Re: Re: Re: Re: Matrix Calculation
Bora Beran Feb 5, 2014 5:16 PM (in response to George Nguyen)
CorrelationCoefficient.twbx 23.0 KB


6. Re: Matrix Calculation
George Nguyen Feb 7, 2014 2:47 PM (in response to Bora Beran)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:
 Indices pill to Row Shelf and Indices (copy) pill to Columns Shelf
 Values pill to Marks Shelf  displayed as Text, then add Quick Calculation for Running Total
 Create ValueAcross calculated field with formula: PREVIOUS_VALUE(WINDOW_MAX(SUM([Value])))
 Create ValueDown calculated field with formula: PREVIOUS_VALUE(WINDOW_MAX(SUM([Value])))
 Drop ValueAcross pill into Measure Values Shelf  computer using Table (Across)
 Drop ValueDown pill into Measure Values Shelf  computer using Table (Down)
 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)))  Drop Correlation Coefficient pill into Measure Values Shelf
I get this table display.
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 Feb 7, 2014 2:57 PM (in response to George Nguyen)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.
~ Bora

8. Re: Re: Matrix Calculation
George Nguyen Feb 7, 2014 3:14 PM (in response to Bora Beran)I tried that as well, but it only returned the value 1.000 for all cells. See below:
I have also attached the workbook so you can see what I did.
CorrelationCoefficient.twbx 23.1 KB


9. Re: Re: Matrix Calculation
Bora Beran Feb 7, 2014 3:22 PM (in response to George Nguyen)1 of 1 people found this helpful 
10. Re: Re: Matrix Calculation
George Nguyen Feb 7, 2014 3:28 PM (in response to Bora Beran)Brilliant!!! Gotta love the intricacies of Tableau.
Thank you so much.

11. Re: Re: Matrix Calculation
Bora Beran Feb 7, 2014 3:32 PM (in response to George Nguyen)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 May 11, 2016 8:40 AM (in response to Bora Beran)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 Feb 1, 2018 8:10 AM (in response to Bora Beran)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.