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]

I believe it won't work like you want it to.

You can only define a single expression per chart, you can't change the expression for each dimension value (i.e. create an expression based on current dimension value, e.g. using dollar sign expansions).

You can create a single expression that checks the dimension value, then switch to an expression to use accordingly (like you've done with your nested if()'s, or similar using pick/ match combination).

[Nicole-Smith · Accepted solution]

To get this to work, I added some load script:

Then on the straight table, I just made the dimension [KPI naam] and the expression is $(vMetrics).

See the attached to see it in action.

[swuehl]

Yes, Nicole, that's a single expression with the mentioned pick/match combination (similar to the nested if() statements Roberto is using).

It's not what I think he is referring to as a 'dynamic expression' (which I believe is not feasable).

[simondachstr]

Have a look at the ValueList(), ValueLoop() functions to create dynamic dimensions. Maybe you'll come up with an alternative displaying your KPI using those.

[Nicole-Smith]

You're correct--it's not feasible loading it in from a file.  I just thought I'd give him something to go off of that actually works, since it's sometimes hard to understand without seeing an example

[swuehl]

To be clear, I am not saying anything against your proposed solution, that's what I also had in mind.

I am just saying, something like this might be a work around for what Roberto was trying to achieve with something I believe just won't work.

[#8]

Nicole and others, Thanks for all the (quick!) answers!

Nicole's approach works in my small project. I wonder: can we say something on performance? would one of the mentioned approaches be preferred (and why)?

Current options are

- nested if

- this pick-match approach

- the valueloop / valuelist (which I find a bit hard to understand; I'll try to find out later...)

It would be great if we can say something on what would be the best approach. Thanks!    

[Nicole-Smith]

You can find some performance info on the pick-match approach here: http://community.qlik.com/message/286947#286947

I've always considered pick-match to be better than nested ifs, but someone can correct me if I'm wrong.

If you're using valueloop/valuelist, you will need nested ifs or pick-match anyway, which just adds more complexity to your solution.

[#10]

Thanks a lot!