Applying a Variable for number formatting to Set Analysis expression

[david_pearson]

Issue with getting the variable to work with set analysis.  using QlikSense Feb 22 Patch 3 version.

Variable vNumberFormats

SQL

=dual(if( $1 > 1000000, num( $1 / 1000000, '£#,##0.0 m'),if( $1  > 1000, num( $1 / 1000, '£#,##0.0k '), num($1, '#,##0 '))), $1)

Current expression is used in a table:

SQL

=sum({<CYTD={'1'},GL_YEAR=,GL_PERIOD=>}GWP)

=$(vNumberFormats(SUM(GWP)) works great, but as soon as i add in set analysis it fails. 
i had thought it would be the following but it fails =$(vNumberFormats(sum({<CYTD={'1'},GL_YEAR=,GL_PERIOD=>}GWP)))

[marcus_sommer · Accepted solution]

Your expression contained commas which are treated as parameter-delimiter within the variable. This means you couldn't use such logic as a general solution. In some scenarios you may use different expressions and/or a different syntax, like:

=sum({<CYTD={'1'}> * < GL_YEAR > * <GL_PERIOD >}GWP)

to avoid the commas but it would increase the complexity if multiple syntax-approaches are in use.

Beside of this such logic could slow down the calculation because your aggregation would be performed six-times instead of one time by a normal expression. By small datasets it may not a big issue ...

In the end there is no really good solution available - means an inbuilt formatting which works everywhere and which might be more customizable (some charts have such feature). 

[david_pearson]

worked as you suggested. thank you!!