How to make dynamic expressions work?

[#1]

Hello QlikCommunity,

I need some advice on how I can best solve my problem.

I have a long list of KPI's (lets say 100) and I want to present these in a chart (lets say a straight table). On the rows, the KPI's are listed, and in the columns the value and target of the KPI.

The problem however is, that the expression of.....
- KPI nr 1 could be an expression with sum (e.g. sum(value) )
- KPI nr 2 could be an expression with avg (e.g. avg(value) )
- KPI nr 3 could be an expression with count (e.g. count(value) )

What I thought that could work, is load my expression (like the ones mentioned above) along with my KPI-definitions, as a separate column in my datamodel, so I can use this (loaded) expression in my chart to calculate the sum, average or count when needed (of course all in 1 and the same column).

I don't get it working! So my questions are:
[1] Who can make this work? (and help me out how)
[2] If the answer on [1] == nobody, please answer if this indeed is not possible

Attached I have an example application, where I have loaded my expressionsin the loadscript, and tried to make it work (but the column indicates only "-" (null).

What the outcome _should_ be is also listed, because an alternative approache _does_ work (the nested if statement)

pseudo:
- if (KPInr = 1;sum(value);avg(value))

But If I implement this nested if statement for about 100 KPI's,
my code looks like spaghetti
I'm afraid that the performance may not be good

Hope you have some advice for me!

The .qvw also is self-explanatory IMO but feel free to ask additional info when things are unclear.

Roberto

PS: other approaches are also welcome, as long as it is a (good) alternative for the nestedIf (100x)

[swuehl]

As a first optimization, you can skip the match() and just use the KPI number:

Let vMetrics = 'Pick([KPI nr],' &  peek('ConcatExpression') & ')';

I am not 100% sure how much better a pick() (/match() ) perform compared to nested if() statements, at least IMHO it's better to read and maintain.

[veidlburkhard]

Hi,

why don't you use this simple solution and take Column(2) as your expression instead of $(=expressie).

This gives the same result as your nested if and should perform 100 times better.

Please try

Regards

Burkhard

[#4]

Hi Burkhard,

I doubt if you understood my problem right!? Column(2) refers to the nested-if-expression (or do I misunderstand you?).

If not: I want to get rid of the nested-if-construction and have another way of calculating.

Column(2) is IMO not a solution, since that REFERS to a method (like the nested if method or the pick(match) method or some other...

If I didn't understand you correctly can you share your .qvw and explain why it would be better / faster?    

[swuehl]

It's giving the same result as the nested if() statements, because it's just referencing the nested if() expression.

Try your chart with the nested if() expression removed.

[veidlburkhard]

Sorry, swuehl,

this was too simple...

[#7]

No problem. I'm already happy with all answers so far.