topic Re: Using Rounded Variables in Expressions in QlikView

Using Rounded Variables in Expressions

Tue, 09 Sep 2014 20:12:53 GMT

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!

Re: Using Rounded Variables in Expressions

JonnyPoole — Wed, 10 Sep 2014 00:30:58 GMT

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

replace

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

Re: Using Rounded Variables in Expressions

ecolomer — Wed, 10 Sep 2014 03:53:29 GMT

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