Dynamic Set Expression

ekech_infomotio — Wed, 13 Jun 2012 07:45:50 GMT

Hi there,

I'm struggling with a little problem where I need to use really dynamic expressions.

OK, let's start.

We've got investment in funds. A fund consists of a number of assets. Assets can have different countries, currencies or classes. And the assets have a market value pre-calculated in EUR.

fund	asset	asset_country	asset_currency	asset_class	market value in EUR
fund_no1	asset_no1	DE	EUR	1	1000
fund_no1	asset_no2	DE	EUR	1	1200
fund_no1	asset_no3	NL	EUR	2	900
fund_no1	asset_no4	DE	EUR	2	1000
fund_no2	asset_no5	US	USD	3	8000
fund_no2	asset_no6	DE	EUR	1	2000
fund_no2	asset_no7	DE	EUR	1	1000
fund_no2	asset_no8	DE	EUR	2	3000
fund_no2	asset_no9	GB	GBP	2	3500
fund_no3	asset_no10	DE	EUR	2	1000
fund_no3	asset_no11	DE	EUR	3	700
fund_no3	asset_no12	US	USD	3	2000
fund_no3	asset_no13	NL	EUR	1	5000
fund_no3	asset_no14	DE	EUR	1	4000

That's the "fact"-part of the story.

Now we can have one or more investmentstrategies per fund, depending on different rules, for example:

Investment in assets from NL

Investment in assets in USD

Investment in assets in Class3

fund	strategy_name	rule
fund_no1	Class3	{$< asset_class = {3} >}
fund_no1	NL	{$< asset_country = {NL} >}
fund_no2	NL	{$< asset_country = {NL} >}
fund_no2	USD	{$< asset_currency = {USD} >}
fund_no3	Class3	{$< asset_class = {3} >}
fund_no3	NL	{$< asset_country = {NL} >}

Now I want to have an overview which rules apply to my funds and what is the share of the assets belonging to this rules. This should look like this:

fund	strategy_name	total per fund	investment according to strategy	share for strategy
fund_no1	Class3	4100	0	0%
fund_no1	NL	4100	900	22%
fund_no2	NL	17500	0	0%
fund_no2	USD	17500	8000	46%
fund_no3	Class3	12700	2700	21%
fund_no3	NL	12700	5000	39%

But how do I get QV to apply my different rules to the corresponding rows in my chart?

Attached is a simplified example with inline-data. Idea was to store my rules as complete "Set Expression"-statement in my strategies table and link this rule dynamically...

Does anyone have a solution for this problem?

regards,

Edgar

topic Dynamic Set Expression in QlikView

Dynamic Set Expression