Evaluate dynamic variables within a variable

roman_blaser — Wed, 27 Feb 2019 10:08:43 GMT

Dear all,

I do have an app, where I reuse expression for a lot of tables, so I created variables to store these, for example a variable vKL_KPI_GEN_ACYTD that calculates the sum of the actuals and vKL_KPI_GEN_BUYTD that calculates the sum of the budget figures and so on.

Now I have to compare the figures dynamically and assign them colors for good and bad, for example actuals vs. budget, actuals vs. forecast and so on. So I created another parametrized variable, which is defined as:

IF( $( $2 ) >= 0,
IF( $( $1 ) >= PICK(ZZZHIGHISGOOD, ZZZLIMIT_GOOD, (2-ZZZLIMIT_GOOD)) * $( $2 ) ,
PICK(ZZZHIGHISGOOD, vC_Figure_Good, vC_Figure_Bad),
IF( $( $1 ) < PICK(ZZZHIGHISGOOD, ZZZLIMIT_BAD, (2-ZZZLIMIT_BAD)) * $( $2 ), PICK(ZZZHIGHISGOOD, vC_Figure_Bad, vC_Figure_Good), vC_Figure_Neutral)),
IF( $( $1 ) >= PICK(ZZZHIGHISGOOD, (2-ZZZLIMIT_GOOD), ZZZLIMIT_GOOD) * $( $2 ),
PICK(ZZZHIGHISGOOD, vC_Figure_Good, vC_Figure_Bad),
IF( $( $1 ) < PICK(ZZZHIGHISGOOD, (2-ZZZLIMIT_BAD), ZZZLIMIT_BAD) * $( $2 ), PICK(ZZZHIGHISGOOD, vC_Figure_Bad, vC_Figure_Good), vC_Figure_Neutral))
)

and is called in diagrams and tables via

$(vI_EVALCOLOR( 'vKL_KPI_GEN_ACYTD', 'vKL_KPI_GEN_BUYTD'))

This does not work however. If I do not use the variable but write

IF( $(vKL_KPI_GEN_BUYTD) >= 0,
IF( $(vKL_KPI_GEN_ACYTD) >= PICK(ZZZHIGHISGOOD, ZZZLIMIT_GOOD, (2-ZZZLIMIT_GOOD)) * $(vKL_KPI_GEN_BUYTD) ,
PICK(ZZZHIGHISGOOD, vC_Figure_Good, vC_Figure_Bad),
IF( $(vKL_KPI_GEN_ACYTD) < PICK(ZZZHIGHISGOOD, ZZZLIMIT_BAD, (2-ZZZLIMIT_BAD)) * $(vKL_KPI_GEN_BUYTD), PICK(ZZZHIGHISGOOD, vC_Figure_Bad, vC_Figure_Good), vC_Figure_Neutral)),
IF( $(vKL_KPI_GEN_ACYTD) >= PICK(ZZZHIGHISGOOD, (2-ZZZLIMIT_GOOD), ZZZLIMIT_GOOD) * $(vKL_KPI_GEN_BUYTD),
PICK(ZZZHIGHISGOOD, vC_Figure_Good, vC_Figure_Bad),
IF( $(vKL_KPI_GEN_ACYTD) < PICK(ZZZHIGHISGOOD, (2-ZZZLIMIT_BAD), ZZZLIMIT_BAD) * $(vKL_KPI_GEN_BUYTD), PICK(ZZZHIGHISGOOD, vC_Figure_Bad, vC_Figure_Good), vC_Figure_Neutral))
)

in the table it works fine.

Any ideas anyone?

Thanks,

Roman

Re: Evaluate dynamic variables within a variable

anushree1 — Wed, 27 Feb 2019 12:48:29 GMT

The way parameterized variables are invoked is not correct, please check the link https://community.qlik.com/t5/QlikView-Documents/Qlikview-Parameterised-Variables/ta-p/1494184

topic Re: Evaluate dynamic variables within a variable in QlikView

Evaluate dynamic variables within a variable

Re: Evaluate dynamic variables within a variable