topic Re: Correlation coefficient with a unique input table in QlikView

Correlation coefficient with a unique input table

Wed, 25 Nov 2020 16:16:04 GMT

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

It is easy if we have these datas:

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:

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 — Thu, 14 Apr 2016 11:18:01 GMT

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

(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 — Thu, 14 Apr 2016 11:22:10 GMT

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 — Thu, 14 Apr 2016 11:29:47 GMT

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

sunny_talwar — Thu, 14 Apr 2016 11:30:57 GMT

True