Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set analysis recursion - kind-of

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. 

1 Solution

Accepted Solutions
Not applicable
Author

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)

View solution in original post

5 Replies
lironbaram
Partner - Master III
Partner - Master III

hi ,

have a look at the attach example , it uses set analysis for benchmark Mv

Not applicable
Author

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.

lironbaram
Partner - Master III
Partner - Master III

hi can you post a sample data

so i can help you with the expression

Not applicable
Author

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

];

Not applicable
Author

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)