topic Re: Parametrized variable not working in App Development

Parametrized variable not working

NLO_CM — Mon, 10 Nov 2025 09:22:50 GMT

Good morning all,

I was looking for some help with this problem I am having with a parametrized variable.

In the editor I define the following variables:

Set vFormatBigNum = Dual(Num($1/ Pow(10, 3 * Div(Log10(fabs($1)),3)),'#.##0,0') & '' & Pick(Div(Log10(fabs($1)), 3),'k','M','B','T'), $1);
SET vNumFormatKPI = if($1 = 'BIGMONEY', '€' & $(vFormatBigNum($2))
, if($1 = 'MONEY', '€' & num($2, '#.##0,0')
, if($1 = 'BIGINTEGER', $(vFormatBigNum($2))
                    , if($1 = 'INTEGER', num($2, '#.##0')
                            , if($1 = 'DECIMAL', num($2, '#.##0,0')
                                , if($1 = 'PERCENTAGE', num($2, '#.##0,00%')
                                    , $2))))));

Which I than use in the sheet for a KPI value:

if(YEAR_TO_DATE_CALC = 'SUM'

, $(vNumFormatKPI(VALUE_TYPE, sum(ACTUAL_VALUE)))

, $(vNumFormatKPI(VALUE_TYPE, avg(ACTUAL_VALUE))))

This worked correctly; However one of the variables I have sometimes has negative values which I do not want to include in the average. So I rewrote the equation to:

if(YEAR_TO_DATE_CALC = 'SUM'

, $(vNumFormatKPI(VALUE_TYPE, sum(ACTUAL_VALUE)))

, $(vNumFormatKPI(VALUE_TYPE, avg(if(KPI = '%Bonus to GGR', if(ACTUAL_VALUE > 0, ACTUAL_VALUE), ACTUAL_VALUE)))))

And now Qlik throws an error, and when you look at the error message most of the question within the average seems to be ignored (see the highlighted part):

How I solve this issue?

Re: Parametrized variable not working

Nagaraju_KCS — Mon, 10 Nov 2025 09:45:19 GMT

try this

which you have highlighted in yellow in the screen shot avg(if(KPI = '%Bonus to GGR')

remove if condition inside, just keep it avg('%Bonus to GGR')

Re: Parametrized variable not working

NLO_CM — Mon, 10 Nov 2025 09:52:26 GMT

But this is what should be there:

avg(if(KPI = '%Bonus to GGR', if(ACTUAL_VALUE > 0, ACTUAL_VALUE), ACTUAL_VALUE))

While

avg(ACTUAL_VALUE)

works correctly

Re: Parametrized variable not working

Nagaraju_KCS — Mon, 10 Nov 2025 10:05:45 GMT

may be last condition should be like, try with add num format

Num(avg(if(KPI = '%Bonus to GGR','##.#0%')))

Re: Parametrized variable not working

marcus_sommer — Mon, 10 Nov 2025 13:04:36 GMT

I think your problem is the nesting of the variables which contain commas within the values (within the expression-string). A comma is always treated as a parameter-delimiter - and there is no masking possible. Theoretically such problems might be resolved with forward/backward-replace approaches with other chars. Often it becomes much to ugly to be sensible.

A better approach is mostly to avoid the comma-stuff by using a different kind of syntax. In your case this might be something like:

avg({< KPI = {'%Bonus to GGR'} > * < ACTUAL_VALUE {">0"}>} ACTUAL_VALUE)

avg(ACTUAL_VALUE) * -(KPI = '%Bonus to GGR') * -(ACTUAL_VALUE > 0)

but it depends on the data-set + data-model + view-requirements if it's equally to the origin condition.

Re: Parametrized variable not working

NLO_CM — Wed, 12 Nov 2025 14:13:01 GMT

Ah is that the issue, not great.. But I found a solution I think, thanks to your suggestions,

I believe this is working:

if(YEAR_TO_DATE_CALC = 'SUM'

, $(vNumFormatKPI(VALUE_TYPE, sum(ACTUAL_VALUE)))

, $(vNumFormatKPI(VALUE_TYPE, avg(ACTUAL_VALUE * ((-(KPI = '%Bonus to GGR') * -(ACTUAL_VALUE > 0)) + -(KPI <> '%Bonus to GGR'))))))