Correlation coefficient with a unique input table

[#1]

I am in trouble to calculate correlation coefficient among two variables.

It is easy if we have these datas:

AAPL	IBM
104.78	134.57
102.15	134.47
100.15	133.80
95.92	131.51

Correl(AAPL,IBM) = 0.94433

But mine are organized in a different way.

I have to calculate the correlation coefficient of Adj Close price between two equities:

Equity	Adj Close
AAPL	104.78
AAPL	102.15
AAPL	100.15
AAPL	95.92
IBM	134.57
IBM	134.47
IBM	133.80
IBM	131.51

How can I calculate Correl with my datas?

I have tried for two days, without results.

I am using a Personal edition so I'm not able to open other user's qlikview file.

Thank you.

[swuehl · Accepted solution]

Maybe create a field that can be used to link the two lines of each Equity that build an x/y pair:

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

returns: 0.94432602

[sunny_talwar]

Or you can use your own approach to creating row now:

Table:

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

  Equity,

     [Adj Close]

FROM

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

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

[swuehl]

Right, but I guess there is a better key to use, maybe a date field or something like this.

[sunny_talwar]

True