Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

decimal roundup when 0.005

Tanai_Goncalves
8 - Asteroid

Hello,

 

I'm rounding a formula.

 

But I need to roundup when it get on the exact half of the third decimal. 

 

155,415 needs to become 155,42 and not 155,41.

 

Example:

 

field 1field 2ResultDesired Round OutputAlteryx Wrong Output
4662,4530155,415155,42155,41
4662,3530155,4117155,41155,41
1145,253038,17538,1838,17
1953,453065,11565,1265,11
1953,43065,1133365,1165,11

 

I'm using this simple formula:

 

round((round([field 1],0.01) /[field 2]), 0.01)

 

Any ideas?

 

Kind Regards,

 

Tanai

16 REPLIES 16
cmcclellan
13 - Pulsar

Does this do what you're after ?

 

CEIL([field 1]*100)/100

dbmurray
8 - Asteroid

I believe this is a floating point issue. 

 

Can you add a very small amount to your number to make it round up?

e.g  round([Result]+0.00000001,0.01)

binuacs
8 - Asteroid

Change the Alteryx output field to int64 data type

atcodedog05
20 - Arcturus

Hi @Tanai_Goncalves 

 

Any value lesser or equal to 5 in the rounding place will get converted to the previous decimal. Anything greater than 5 ex:6 will be converted to next decimal. Refer the highlighted

 

atcodedog05_0-1632207888134.png

 

Hope this helps : )

atcodedog05
20 - Arcturus

Hi @Tanai_Goncalves 

 

You can arrive at the desired output by using below formula.

 

Workflow:

atcodedog05_0-1632208165314.png

Hope this helps : )

mst3k
11 - Bolide

@Tanai_Goncalves - perhaps you should submit this as a bug. i could understand if it's a floating point issue, but i'm not sure it is. they may just need to fix their round() function to make the exact midpoint round UP to the desired level instead of down.

 

this is not intuitive and i would not expect people to need to add an arbitrary tiny number to force the round() to work as expected. i'm kind of concerned now about any previous times i used the round function??

atcodedog05
20 - Arcturus

Hi @mst3k 

 

I have same concern excel seems to round anything lesser than 5 to previous greater than equal to 5 to next. This is definitely a risky issue. Especially when you dont know about this issue and have used the round functionality this is for both round and as well as fixed decimal.

 

atcodedog05_0-1632209203902.png

 

danilang
18 - Pollux
18 - Pollux

Hello All

 

As @mst3k hinted at, this is actually a floating point issue.  If you force the values to fixed(19.15) you get this 

 

danilang_1-1632224419781.png

 

 

The Alteryx Round() function(the Round column above) uses the underlying floating values and not the displayed values in its calculation leading to the inconsistencies that you see here.  Since excel was developed for financial applications, where rounding follows very specific conventions,  it actually has code that operates on the string representation of the number to ensure consistent results.  

 

As @dbmurray suggested the best way to get around this is to add a very small amount to the number before applying the round function.   The double format used in the Alteryx Engine stores number to about 15 decimal digits of precision so adding 0.000000000001 should guarantee consistent results for rounding(Round+).

 

Dan 

 

 

 

 

Tanai_Goncalves
8 - Asteroid

Hello.

 

Thanks for replys.

 

Unfortunately none of the answers worked.

 

I have really no idea what to do. I need this precision.

 

Kind regards,

 

Tanai

Labels