dynamic variables using dollar sign expansion

[davetrentwipro]

In a straight table (or pivot table) with KPI as a dimension I define a measure having the function  ='vCurrent' & $(='$(vKPI)')  and have text returned in each cell as 'vCurrentID01', 'vCurrentID02', etc. for all KPIs.

The problem is that if I try to use the result as a variable by defining another measure having the function like =$(=$(='vCurrent' & $(='$(vKPI)')))  I'd hope to get the value of the associated variable.

It works fine when I'm filtered on one KPI, but blows up otherwise.  All vKPI is doing is Only(_KPI_ID)

I've successfully substituted out the vKPI variable in the function having $(=$(='vCurrent' & Only(_KPI_ID))) which again works fine when only a single KPI is filtered, but doesn't work fine otherwise.

I created a variable 'vCurrent' to confirm nulling out the dynamic part was the issue.  It baffles me why though the table can build a string but can't access a variable built from that string unless I filter to one KPI.

Thoughts?

At this point I'm fixated on "why doesn't this work!?!"  I have some alternate approaches to try out to solve the "real" problem in the app, but I'd really like to understand why this doesn't work 🙂  (so at least for now, please no 'why are you trying to do this' questions, thanks!)

[marcus_sommer · Accepted solution]

Qlik doesn't evaluate any field- or variable-values as expressions - they are always plain strings. The only way to bypass it is the use of a $-sign expansion which creates an adhoc-variable which is always evaluated before the object is calculated (on a global level without regarding to the dimensions in the object) and this single result is applied to all rows.

This means your approach couldn't work. If you really want to use multiple different expressions within a column you need to apply the following main-logic:

if(Dim = 'a', expA, if(Dim = 'b', expB, ...

These logic could be optimized with pick(match()) whereby the listings within it could be loaded from a table and called with something like this: $(=concat(MatchValues, ',', SortField))

- Marcus

[davetrentwipro]

Thanks for the thoughtful response Marcus!  What you described certainly matched what I observed.

Unfortunately, the table-ized pick(match) you described is the structure I inherited.  It worked acceptably in most other apps, but is a performance dog in this app.

The "value" calculation performs (mostly) acceptably, but its the threshold comparison / coloring portion that accounts for most of the drag.  Its the 76 (!) part if/match combo in this one app that I (unsuccessfully) tried to replace with the dynamic variable.

My remaining two options should result in some performance improvement, just not sure yet if it'll be enough.  <crossing fingers>

[marcus_sommer]

I assume that the way how the multiple expressions are assigned to the rows/columns is rather not essential to the performance else the pure amount of them and of course the way in which they are defined and are interacting with the datamodel.

This means you might need some changes there to improve the performance. For example developing the datamodel more in the direction of a star-scheme or even a big fact-table, or pre-calculating something within the script to simplify the expressions or ...

- Marcus