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

# Matrix Calculation

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

What's the best way to handle this?

• ###### 2. Re: Re: Matrix Calculation

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

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

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.

• ###### 5. Re: Re: Re: Re: Matrix Calculation

Hi George,

Is this what you're looking for?

~ Bora

• ###### 6. Re: Matrix Calculation

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.

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

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

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.

• ###### 9. Re: Re: Matrix Calculation

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

Brilliant!!! Gotta love the intricacies of Tableau.

Thank you so much.

• ###### 11. Re: Re: Matrix Calculation

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

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

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.