Parametrized variable not working

[NLO_CM]

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?

[Nagaraju_KCS]

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')

[NLO_CM]

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

[Nagaraju_KCS]

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

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

[marcus_sommer · Accepted solution]

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)

or

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.

[NLO_CM]

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'))))))