topic Re: Calculate correlation coefficient among all pairs of equities in QlikView

Calculate correlation coefficient among all pairs of equities

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculate correlation coefficient among all pairs of equities

sunny_talwar — Thu, 14 Apr 2016 13:51:31 GMT

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])

Re: Calculate correlation coefficient among all pairs of equities

Thu, 14 Apr 2016 14:53:00 GMT

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:

Re: Calculate correlation coefficient among all pairs of equities

sunny_talwar — Thu, 14 Apr 2016 15:00:18 GMT

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

Re: Calculate correlation coefficient among all pairs of equities

Thu, 14 Apr 2016 15:07:00 GMT

Thank you for your patience. It works perfectly!