Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using Rounded Variables in Expressions

Hello all! I have a question about rounded variables...

I've created 2 variables, Variable A and Variable B, and in the expression I've rounded the number to 1 decimal place ($8.1M, for example).

I then created a text box that will display different text based on whether Variable A is >, <, or = Variable B.

The catch is that I want to determine whether ROUNDED Variable A is >, <, or = ROUNDED Variable B, because very likely, Variable A will never be exactly equal to Variable B.

For example:

Variable A = $8,059,000

Rounded Variable A = $8.1M

Variable B = $8,112,000

Rounded Variable B = $8.1M

Technically, in this example, Variable A < Variable B, however, in the text box I want them to be shown as equal because of the rounding factor. Does anyone know how to create a formula that takes into account the rounded numbers?

My actual equation is here:

='Total ' &

if(vA>vB,

  'is growing, increasing from '& vB & ' to ' & vA,

  if(vA<vB

,'is declining, shrinking from '& vB & ' to ' & vA

  'has not materially changed in the last period')

I'd appreciate any help!

2 Replies
JonnyPoole
Employee
Employee

use the round() function with a divide by 1,000,000

replace

vA

vB

with

round(  vA / 1000000 , .1) * 1000000

round(  vB / 1000000 , .1) * 1000000

(I think for comparison purposes you actually don't need to multiple by 1000000 again. the 2nd parameter indicates i'm rounding to 1 decimal place. rounding is different than formatting to 1 decimal)

so....

='Total ' &

if(  round(  vA / 1000000 , .1) * 1000000  >    round(  vB / 1000000 , .1) * 1000000,

  'is growing, increasing from '& round(  vB / 1000000 , .1) * 1000000 & ' to ' & round(  vA / 1000000 , .1) * 1000000,

  if( round(  vA / 1000000 , .1) * 1000000< round(  vB / 1000000 , .1) * 1000000

,'is declining, shrinking from '& round(  vB / 1000000 , .1) * 1000000 & ' to ' & round(  vA / 1000000 , .1) * 1000000

ecolomer
Master II
Master II

I you have rounded the variables in your creation, the value of both are equal, ($8,1M). you can try putting in a text box and the value of another variable with 2 decimal format aver that result.

But if you have variable with their original value A is less than B

I suggest you keep the values ​​of A and B and original rather than rounding formatees field when present, and in this case, use 2 decimals for the text consistent