Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

correlation matrix

I am trying to build the following correlation matrix:

nasdaq dow jones sp500

sp 500 .9 .7 1

nasdaq 1 .9 .8

dow jones .8 1 .7

Furthermore, if we add a new index to this matrix it need to automatically expand into a 4x4 table. So this is a dynamic correation grid. I tried to build this matrix with pivot table and straight but it s not working. Does anyone have any advice or example that can help me build such a matrix?

4 Replies
seebach
Luminary Alumni
Luminary Alumni

The data your loading should something like this:

From To Value

sp 500 nasdaq .9

sp 500 dow jones .7

nasdaq dow jones .9

nasdaq sp500 .8

Then you can put From and To into a Dimension and value into your Expression. Then drag the To column to the top of the Expression colunm to make it a vertical one.

Not applicable
Author

Here is where diffculty comes in. The table which stores the data is of the following format:

Indexdtemonthgross_profit
sp500200901539,754.75
sp5002009024,910,985.95
sp500200903(6,364,076.03)
sp500200904(181,889.80)
sp500200905(2,440,912.43)
sp500200906(666,537.74)
sp500200907583,795.79
sp500200908536,873.27
sp500200909(850,797.58)
sp5002009102,318,123.49
sp5002009112,686,174.42
sp5002009122,403,489.61
sp500201001(4,061,927.86)
sp500201002(1,045,574.43)
sp5002010033,720,635.60
sp500201004(1,723,617.73)
sp500201005(5,725,351.98)
nasdaq2009012,348,165.83
nasdaq2009021,757,471.49
nasdaq200903(3,712,985.59)
nasdaq200904(1,642,114.95)
nasdaq200905(618,843.31)
nasdaq200906(246,316.86)
nasdaq200907633,128.89
nasdaq2009083,639,483.78
nasdaq2009094,211,887.41
nasdaq200910(2,890,561.41)
nasdaq2009111,551,894.78
nasdaq2009124,439,487.28
nasdaq201001(7,853,511.23)
nasdaq201002194,673.15
nasdaq2010032,899,889.13
nasdaq201004(1,921,281.96)
nasdaq201005(5,333,947.39)
Dow Jones2009013001033.86
Dow Jones200902(889,982.86)
Dow Jones200903(5,159,477.59)
Dow Jones200904(333,957.88)
Dow Jones2009053,258,313.13
Dow Jones200906809,931.96
Dow Jones2009071,811,683.13
Dow Jones2009081,892,011.97
Dow Jones2009097,913,481.36
Dow Jones2009103,833,387.92
Dow Jones2009111,950,252.27
Dow Jones200912(4,193,824.94)
Dow Jones201001(3,292,703.77)
Dow Jones201002(321,177.78)
Dow Jones2010033,511,359.65
Dow Jones2010042,505,688.59
Dow Jones201005(8,533,409.84)
Russel200901(876,413.27)
Russel200902(2,930,873.40)
Russel20090310,159.69
Russel200904(785,864.11)
Russel200905(222,896.64)
Russel200906311,802.19
Russel200907(1,197,548.26)
Russel200908(1,509,318.96)
Russel200909(251,153.85)
Russel200910(732,472.95)
Russel200911(569,263.16)
Russel200912(1,055,649.36)
Russel201001109,633.41
Russel201002(76,332.84)
Russel201003(63,395.09)
Russel201004(952,521.79)
Russel201005(2,865,012.61)

I need to build a dynamic correlation matrix between sp500 vs nasdaq, sp500 vs russel, nasdq vs russel etc (see grid above). The data is in a form that doesnt facilate correlation caluclation (each index doesnt have it's own column). The matrix needs to be dynamic. If a new fund is added to this list the matrix will automatically expand. The matrix will become a 5x5 matrix. Does anyone have a solution to this problem or example that I can look at?

johnw
MVP
MVP

Well, I don't guarantee that this is it, but the numbers look... plausible? I simply added a duplicate table connected to the original by the month, then correlated the gross_profit in both tables using both indexes as dimensions.

pgdavis2
Partner
Partner

Great solution! This is extremely useful Smile

Thanks!