Calculate correlation coefficient among all pairs of equities

[#1]

I have some Equities (50+), for example:

Equity
AAPL
GOOG
IBM
APC
XOM
.............
.............

For each Equity I have its adj Close in some dates, for example:

Equity	Adj Close
AAPL	104.78
AAPL	102.15
AAPL	100.15
AAPL	95.92
GOOGL	741.84
GOOGL	742.58
GOOGL	743.61
GOOGL	726.39
IBM	134.57
IBM	134.47
IBM	133.80
IBM	131.80
APC	49.07
APC	48.46
APC	43.72
APC	40.06
XOM	76.76
XOM	77.41
XOM	76.77
XOM	75.54

My target is to calculate the correlation coefficient among all pairs. I am able to calculate the correlation coefficient between two specific equities as you can see in the answer of one of my previous post:

LOAD If(Equity <> previous(Equity),1,Rangesum(1,Peek(RowNo))) as RowNo,

     Equity,

     [Adj Close]

FROM[https://community.qlik.com/thread/213200]

(html, codepage is 1252, embedded labels, table is @2);

Then:

=Correl( aggr(Only({<Equity = {AAPL}>}[Adj Close]), RowNo ),aggr(Only({<Equity = {IBM}>}[Adj Close]), RowNo) )

Correl function works perfectly with "AAPL" and "IMB"; but I have a lot of equities and I want to calculate the coefficient for all pairs; I 'can't' do this "by hand" because it is a really slow work.

How can I solve?
Thank you.

[sunny_talwar · Accepted solution]

Try this may be:

Table:

LOAD AutoNumber(RowNo(), Equity) as Key,

  Equity,

    [Adj Close]

FROM

[https://community.qlik.com/thread/213273]

(html, codepage is 1252, embedded labels, table is @2);

Left Join (Table)

LOAD Key,

  Equity as _Equity,

  [Adj Close] as [_Adj Close]

Resident Table;

Dimension: Equity and _Equity

Expression: =Correl([Adj Close], [_Adj Close])

[#3]

Great answer. It works. Just a question about rendering: i'm not able to open .qvw since I'm using a personal edition.
How can I get your table?

I use a pivot table chart and this is what I get:

[sunny_talwar]

Just drag the _Equity Dimension to the right top corner and it will be pivoted to go across

[#5]

Thank you for your patience. It works perfectly!