Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
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])
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:
Just drag the _Equity Dimension to the right top corner and it will be pivoted to go across
Thank you for your patience. It works perfectly!