Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
david_pearson
Contributor III
Contributor III

Applying a Variable for number formatting to Set Analysis expression

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)))
 
Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

2 Replies
marcus_sommer

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
Contributor III
Contributor III
Author

worked as you suggested. thank you!!