Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Employee
Employee

Re: Using Rounded Variables in Expressions

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
Honored Contributor II

Re: Using Rounded Variables in Expressions

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

Community Browser