I want to use an expression stored in a table

[#1]

Hi,

I have trouble figuring out how to solve this scenario where my calculation is stored in the table I am using. My Qlik table looks like this:

Dimension        CalculationToUse

L1               PostAmt*L1

L2               PostAmt*L2

L3               PostAmt*L3

Etc..there are 26 unique dimensions and the same number of expressions. I could use an IF-statement, and it works, but is REALLY slow...I would prefer to find another way. It is to be used in a Set analysis like this one:

Sum({$<

YearPeriod={"$(=Year(Max(PostDate)) & num(Month(Max(PostDate)),'00') )"}

>}[CalculationToUse]

)

The third line in the above statement is where I have problems. Is this at all possible?

[marcus_sommer]

You couldn't use external stored expressions in this way. To get them recognized as expression you need $-sign expansion like this one: $(=[CalculationToUse]) but this won't calculated on row-level else once global before the chart is calculated and applied for each row.

The only way to solve cases like this is to use conditional functions like if whereby the following pick(match()) will be a lot performanter as many nested if-loops:

Sum({$<

YearPeriod={"$(=Year(Max(PostDate)) & num(Month(Max(PostDate)),'00') )"}

>} Postamt * pick(match(Dimension, 'L1', 'L2', 'L3'), L1, L2, L3))

- Marcus