Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a problem for which my understanding was set analysis was the perfect solution, but I can't seem to solve it. Let me provide some preface on the data model and what I need to do then ask my question:
I have a fact table of positions. These are positions of both portfolios and benchmarks. the field here i'm chiefly interested in is market_value. I have a dimension table that describes the portfolios and benchmarks. I also have another table which maps portfolios to benchmarks. I have attempted joining this to the dimension table and using as an island mapping table but can't seem to get the set analysis right regardless.
The chart i need to create (this is the short version) is:
portfolio_id(dim) | sum(market_value) of portfolio_id(dim) | benchmark_id(dim) where this is found in the relational map to the portfolio_id | sum(market_value) of benchmark_id(dim)
Fact:
id | mv
P1 | 50
P1 | 100
B5 | 60
B5 | 80
Dim:
id | name
P1 | ASDF
B5 | QWER
the map:
Portf | Bench
P1 | B5
Chart: (when selecting P1 from a list box)
ASDF | 150 | QWER | 140
I'll need to extrapolate on the solution, but the basics should provide the understanding to extrapolate. The last field in the final chart is my road block.
In order to get to the results I needed, I altered the data model instead of relying on expressions. My steps are below:
make a copy of the Map table
join the bench from the fact to one map table
join the portf from the fact to the other map table
join the two map tables together
Split the DimP in half (one for portf, one for Bench)
Leave the DimS as is.
Splitting the map in half and joining the two sides to the map first, ensured that for every benchmark record the portfolio was identified (for securities that are only held in the Bench and not the Diff). This is necessary to properly group by the Portf and include all of the related Bench in that grouping.
I could then do this for both portfolio market value and bench market value:
sum(MVB)/sum(total <Portf> MVB)
sum(MVP)/sum(total <Portf> MVP)
hi ,
have a look at the attach example , it uses set analysis for benchmark Mv
For those that can't access the example, the formula Liron gave is sum({<ID=p(Bench)>} total Mv) with 2 dimensions (PortID and BenchID).
Thanks Liron; however...
At this point I need to expand my problem as it seems the simplest example is too simple for my needs. The first qualm is that I need to report on more than one portfolio at a time, extending the final chart to this:
Chart: (when selecting P1,P2,P3 from a list box)
ASDF | 150 | QWER | 140
P2N | 200 | B1N | 350
P3N | 130 | QWER | 140
the total modifier in the above expression will not work when expanding the use case in this manner. As a note, a single benchmark could be related to more than 1 portfolio.
I'll go ahead and not the further expansion of the use case here as well. The chart should be a pivot table as the first dimension is portfolio_id, the second is asset_type. asset_type comes from a second dimension table which describes the positions/securities. The end goal being to alter the market_value computation into a % of portfolio thus seeing the asset allocation differences between the portfolio and it's benchmark.
Chart: (when expanding above into assets (not taking the step of percentage))
ASDF | 150 | QWER | 140
T1 | 75 | QWER | 140
T2 | 75 | | 0
P2N | 200 | B1N | 350
T1 | 75 | B1N | 150
T2 | 0 | | 50
T3 | 75 | B1N | 150
P3N | 130 | QWER | 140
T1 | 80 | QWER | 140
T3 | 50 | | 0
Chart: (when expanding above into percentage)
ASDF | 100% | QWER | 100
T1 | 50%| QWER | 100%
T2 | 50%| | 0
P2N | 100% | B1N | 100%
T1 | 75% | B1N | 42.8%
T2 | 0% | | 14.2%
T3 | 25% | B1N | 42.8&
P3N | 100% | QWER | 100%
T1 | 61.5%| QWER | 100%
T3 | 38.4%| | 0
Edit: final chart was incorrect.
hi can you post a sample data
so i can help you with the expression
well, tried attaching qvw but work won't let me. Here are the inline statements used in that model.
Fact:
LOAD * INLINE [
idP, idS, MV
P1, S1, 50
P1, S2, 50
P1, S3, 100
P2, S3, 40
P2, S4, 80
P2, S2, 20
P3, S1, 60
P3, S4, 70
P3, S5, 50
P3, S2, 50
B1, S1, 50
B1, S3, 60
B1, S5, 70
B2, S9, 100
B2, S1, 70
B2, S8, 90
B2, S3, 150
B2, S4, 40
];
DimP:
LOAD * INLINE [
idP, NameP
P1, P1Name
P2, P2Name
P3, P3Name
B1, B1Name
B2, B2Name
];
DimS:
LOAD * INLINE [
idS, AssetType
S1, T1
S2, T1
S3, T2
S4, T2
S5, T3
S6, T3
S7, T1
S8, T2
S9, T3
];
Map:
LOAD * INLINE [
Portf, Bench
P1, B1
P2, B2
P3, B1
];
In order to get to the results I needed, I altered the data model instead of relying on expressions. My steps are below:
make a copy of the Map table
join the bench from the fact to one map table
join the portf from the fact to the other map table
join the two map tables together
Split the DimP in half (one for portf, one for Bench)
Leave the DimS as is.
Splitting the map in half and joining the two sides to the map first, ensured that for every benchmark record the portfolio was identified (for securities that are only held in the Bench and not the Diff). This is necessary to properly group by the Portf and include all of the related Bench in that grouping.
I could then do this for both portfolio market value and bench market value:
sum(MVB)/sum(total <Portf> MVB)
sum(MVP)/sum(total <Portf> MVP)