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?
Trying to understand what you have done. Would you be able to share the logic behind the Cartesian table? How do you create that table?
Also did you see any performance benefits of implementing it this way? (It uses aggregated calculated dimension and also uses a complicated expressions, would it help with performance or would it be better to use the other alternative of using mirror table?)
I really like this approach, and I am trying to automate and implement with a large set of data. To better understand how you did this, I have some questions. Am I correct to assume that 'dim' is the dimension? And 'fact' is the factor? Is that the value of the parameter 'A'?
Because my data set is very large, I'm trying to figure out how to automate this in my script. I have 336 Fields in my data. So my cartesian table would need to be 336 x 336. Once complete, I could crosstable to convert it to the format you have with dim, id, number.
Any thoughts on how to automate this? Would a for each loop work with two indexes, e.g. i, j.
