Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
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!!