Qlikview IF statement & AGGR in variable parameter not working

[Enrique]

Hey everyone,

I built a variable to dynamically alter the number formatting of whatever expression I use it with, by means of a variable parameter. It works perfectly on normal expressions, but seems to completely break when there are IF conditions and/or AGGR functions within the expression. I'm running on Qlikview 11.20

Variables used:

vR=Just a button to alternate between the number formats

vNumberFormat=

Pick(Match($(vR), 0, 1, 2, 3),
'#,##0  ;(#,##0)',
'#,##0.00  ;(#,##0.00)',
'#,##0.0  ;(#,##0.0)',
'#,##0  ;(#,##0)')

vDivide=

Pick(Match(vR, 0, 1, 2, 3),
'1',
'1',
'1000000',
'1000')

vNumFormat=

Num(($1 / $(vDivide)), '$(vNumberFormat)')

Example of an expression using the variable which returns an error:

=$(vNumFormat(if(Dimensionality()=1,

Aggr(RangeSum(Above(sum({<FIN_YEAR={$(vCURRENTYEAR)}>} IS_ACTUAL_MTD),0,RowNo(TOTAL))),[TOTAL I/S]),

if(match([IS LEVEL 1],'Income', 'Equity-accounted earnings'),sum({<FIN_YEAR={$(vCURRENTYEAR)}>} IS_ACTUAL_MTD),
if(match([IS LEVEL 1],'Expenses','Finance charges', 'Tax'), sum({<FIN_YEAR={$(vCURRENTYEAR)}>} IS_ACTUAL_MTD)))

)))

Any help would be appreciated, thank you.

[stevejoyce]

i would expect passing commas being a problem, as this is the special character to seperate different parameter inputs.

try to pass something else, like ;, and your variable can be wrapped in a replace(<exp>, ';', ',')

[Enrique]

Thank you for the reply.

While that does fix some of its issues, it still seems to be expecting a "missing" ')'.

It also seems to not see it as numeric anymore either after the replace.

[marcus_sommer · Accepted solution]

What you are trying to do is not possible - at least not in a sensible way - because the comma-treatment as parameter-delimiter will always cause trouble.

Often you may find technically ways to replace functions which needs parameter with some without them and/or write them in another syntax and/or applying any comma-replacement - but such measures could become quite complex, much more as originally logic and they will mostly remain particular solutions - means not applicable as a general approach.

Therefore keep it as simple as possible and just use it as:

Num(YourExpression / $(vDivide), '$(vNumberFormat)')

- Marcus

[Enrique]

That is what I ended up going with, though I had still wondered if there could have been a more elegant solution. Now I can sleep soundly, thank you!