Correlation matrix based on selection

[Anonymous]

I have a table with the following columns: portfolio name, return date (month), return. There are over 1000 portfolios each with about 60 return dates and return. The correlation between two portfolios is simply correl(returns from portfolio 1, returns from portfolio 2). How do I create a matrix with every combination of two portfolios from the portfolios selected (say the user selects 10 portfolios, I would have a 10 by 10 diagonal matrix).

I've seen (on this forum), the solution of creating a duplicate table, which would work if I wanted a matrix with every portfolio, but how do I limit the second dimension to just the portfolios selected?

Any help would be appreciated. 

Thanks.

Joao

[#2 · Accepted solution]

Hi Joao

A good solution would be the one you suggested, to create a mirror table to use as the other dimension in your matrix.

In order to limit the second dimension to the portfolios selected, you could set an action everytime the first portfolio dimension is selected.

Go to settings -> document properties  and click on the "Triggers" tab

In the "Field event triggers" list box, select the original portfolio dimension. Add an action to run when it a selection is made by clicking on the "add action" button under the "on select" heading.

You then want to add a "select in field" action. Set the name of the field to be the name of the "mirror" field that holds the duplicate portfolio names. Under "search string" type the following expression:

='('&GetFieldSelections([Portfolio_Name_field],'|',10000000)&')'

Then okay everything. When you click on the first portfolios, the same selection should be made in the second ones.

Why does this work?

In the selection string you can specify a number of items, in brackets, separated by pipes '|'. EG

(Apples|Pears|Bananas) in a select in field action will make a selection for those three fruits in the field.

My expression getfieldselections([Portfolio_Name_field],'|',10000000) returns the selections in the first dimension with the pipe as a delimiter. This then needs to be enclosed in brackets to mimc the list of items usually required as input.

The number at the end of the expression sets the maximum number of selections that is returned in this string before describing the selection as numbers. EG sometimes "34 out of 100" is displayed rather than a list of items.

Although you only wish to select around 10 portfolios, so this shouldnt be a problem!

Hope this helps

Erica

[Anonymous]

Thanks Erica, I will try this out. I didn't realize that this functionality was even available. This will allow me to do a whole lot more that I didn't think was possible.

Sent from my iPad

[Anonymous]

Hi Erica

Thank you so much for this solution. It appears to work except for a complication that I'm hoping you have a solution for.

Where my portfolio names have spaces in them, it doesn't work. Is there a way to resolve this?

Joao

[Anonymous]

Hi Erica

Please ignore my complication. I just added double quotes to the beginning, delimiter, and end of the statement that you gave me and it appears to sort it out.

Thanks again for this solution, it works perfectly.

Is there anything Qlikview can't do?

Joao

[#6]

QlikView can't make me toast, but that's about it

Everything else is work-aroundable!

Erica

[#7]

Hello,

i had some excercise with making 2 dimension chart on data from one table.

One dimension was normal dimension and 2-nd was callculeted dimension (made from the same attribute).

I had one table with costs and profits documents and using this approach it was able to show, what was share of costs documents within pofit documents (using association with products in logistic chain).

I feel, that using this approach it may be possible to callculate correlation, making cartesian of elements from one table.

Of course main benefit may be to avoid load the same data twice, what may be important if our table is big. I feel also, that it will be nothing to do with selection on both dimension ....

I will try and share results if somebody is interested.

Regards

Darek

[Anonymous]

Great. Let us know if you succeed.

Joao.

[#9]

So, i prepared small example.

You will find 2 charts.

One is "correl matrix without mirror table" (callculated with aggr and set analysis, from correlation equation). Unfortunetely, this was not able (i didnt found a way to use build correl function).

2-nd chart "correl matrix with mirror table" (callculated with build-in correl function - to have possibility to check results of first chart).

I didn't try which method callculates faster (i think, method with mirror table may be faster, even much faster, because of complicated formulas in method without mirror). But anyway it was nice to make this excercise and find another method

My method advantages:

- dont need to duplicate data (fact data)

- this may be way to callculate other not built-in statistical functions

- selection copying is not needed

Disadvantages:

- need small additional table (cartesian of dimension)

- much more complex callculations/expressions

So, if input data set is not big, i think, that method with mirror table is more ellegant

It will be nice, if somebody will try my method with bigger set of input data

Reagards

Darek

[Anonymous]

Sorry, too complicated for me!