Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Correlation coefficient with a unique input table

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

It is easy if we have these datas:

AAPLIBM
104.78134.57
102.15134.47
100.15133.80
95.92131.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:

AAPL104.78
AAPL102.15
AAPL100.15
AAPL95.92
IBM134.57
IBM134.47
IBM133.80
IBM131.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.

1 Solution

Accepted Solutions
MVP

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,

FROM

(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

4 Replies
MVP

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,

FROM

(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

MVP

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

Table:

Equity,

FROM

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

MVP

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

MVP

True

Community Browser