topic Re: set analysis recursion - kind-of in QlikView

set analysis recursion - kind-of

Wed, 21 May 2014 14:12:23 GMT

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.

Re: set analysis recursion - kind-of

lironbaram — Wed, 21 May 2014 14:26:40 GMT

hi ,

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

Re: set analysis recursion - kind-of

Wed, 21 May 2014 14:47:52 GMT

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.

Re: set analysis recursion - kind-of

lironbaram — Wed, 21 May 2014 17:34:58 GMT

hi can you post a sample data

so i can help you with the expression

Re: set analysis recursion - kind-of

Wed, 21 May 2014 17:39:41 GMT

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

];

Re: set analysis recursion - kind-of

Fri, 23 May 2014 13:13:04 GMT

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)