Alteryx Odd Behavior

Hello everyone,

I have been pretty odd behavior from Alteryx on rounding numbers. At first I thought there was something wrong with the calculation, but apparently it is a defect. So I wanted to share this with the community to get feedback. I tested it on version 9.5, 10.5 and 11

so if you take an average of two numbers of (100, and 124.83) it will end up in 112.415. You can either do it manually or use function "average".

Now when you try to round the result to hundredth with round ( [X] , 0.01) it will end up at 112.41 instead of 112.42

Interesting part is if you code the number 112.415 manually then the result of rounding will be 112.42

It gets more interesting when you take the average of 100 and 224.83 instead of 124.83. The result of course is 162.415 and if we round it this time it will end up as 162.42. in other words "whole number" is affecting the decimal rounding which does not make sense to me.

I guess this should be probably a defect on the way that numbers are restored.

Fun fact: we found this issue on Halloween !

Common Use Cases

Accepted answers

KevinP

@Pasha_Maleknia This isn't a bug, but rather a limitation with how computers store and perform math operations on floating point numbers. Let's consider the number '1.025'. Due to limitations in precision with variable storage and the conversion to binary when this value is stored as a double the number actually gets stored as '1.024999999999999911182158029987476766109466552734375'. As such when rounded to 2 decimal places the number would round to 1.02 where you may expect it to round to 1.03. This is because computers can't store floating point values with exact precision, and some fractions are infinite in length (1/3 for example is 0.33333... repeating). Because of finite storage space if the value doesn't convert to an exact binary number or if it exceeds storage available for the variable it gets rounded. There is an excellent article here on the Community written by one of our developers explaining this in detail. I have included a link for that article below along with some third party article that go into more of the math behind the behavior.

Community Article:

Floating point numbers are surprisingly strange

Additional reading:

Why 0.1 Does Not Exist In Floating-Point

Inconsistent Rounding of Printed Floating-Point Numbers

Stack exchange discussion with a good simple explanation:

What causes floating point rounding errors

KevinP

@TeePee The rounding isn't inconsistent and it also isn't incorrect. As I previously stated this comes down to how fractional number are stored in binary by computers. For details on how this works I highly recommend reading the resources I linked to in my previous post.

As for your specific example none of the rounding you performed is wrong. It just wasn't what you expected based on the input and because you aren't considering the real value of the number as stored by the computer. For example 206.95 when stored as a double in binary is actually '206.94999999999998863131622783839702606201171875' and when round at the first decimal place 206.94 becomes 206.9. 1.45 when stored as a double is actually '1.4499999999999999555910790149937383830547332763671875' which rounds as you saw to 1.4, and 1.55 when stored as a double is '1.5500000000000000444089209850062616169452667236328125' which rounds to 1.6. So the numbers are rounding exactly as you are telling them to in your formula, its just the that underlying number being used by the formula isn't what you expect because the number can't be stored exactly by the computer. As such the computer stores the number as the closest possible approximation that fits into the designated memory space. Sometimes this value is slight smaller than the actual number and sometimes it slightly larger.

To get the results you want/expect you need to be more precise with your rounding so that the stored value is greater than 206.950 when your do your final rounding. You can easily do this by rounding the number multiple time with multiple decimal places of precision prior to rounding at the single decimal place you want for your end result. See the screenshot below for an example.

If you are curious what a specific decimal value translates to when stored as a float or double by a computer you can use this link to find the actual value.

Floating Point Converter

All comments

KevinP

Community Article:

Floating point numbers are surprisingly strange

Additional reading:

Why 0.1 Does Not Exist In Floating-Point

Inconsistent Rounding of Printed Floating-Point Numbers

Stack exchange discussion with a good simple explanation:

What causes floating point rounding errors

TeePee

I'm having a similar issue with rounding. (Sample workflow attached and images pasted below)

There doesn't seem to be consistency in how decimal numbers ending in a 5 are rounded. In my test, two numbers were incorrectly rounded down and one was correctly rounded up. All us end-users expect to see "5 or more, raise the score. 4 or less, let it rest."

I understand the explanation, but my question is, regardless of the why, how do we get Alteryx to do what we expect it to do! Why is it that Excel can handle rounding? Has anyone discovered a workaround they'd be prepared to share?

Many thanks in advance

Rows 2, 5 and 7 need to roundupCalculation to round to 1 decimal placeRows 2 and 5 are wrong; row 7 is right

Formula ROUND testing.yxmd

Tim_Slade

Coming late to the party.

I've just run in to this issue as well.

I am unhappy with the response that 'floating point is hard'.

To solve this issue in my workflow dealing with financial data, I resorted to using the Python tool, in order to use Pandas to calculate the amount correctly.

The Python, for those who may wish to do this as well, is:

from ayx import Package

from ayx import Alteryx

import pandas as pd

data_to_manipulate = Alteryx.read("#1")

data_to_manipulate.column_that_needs_dividing = data_to_manipulate.column_that_needs_dividing.div(100)

Alteryx.write(data_to_manipulate, 1)

If you want to round your number, you can use this

data_to_manipulate.column_that_needs_dividing = data_to_manipulate.column_that_needs_dividing.div(100).round(2)

as the 5th line

The results that I got from this were as I expected them to be.

Alteryx: 19/100 = 0.189999997615814

Pandas: 19/100 = 0.19

Alteryx is a product that we pay for, Pandas is made available for free, and one of them has solved this issue.

jdminton

I know this is an older post, but I wanted to share the solution with those who've run into this issue for rounding in the past. https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Macro-for-rounding-Hint-Alteryx-Round-does-NOT-do-this-correctly/m-p/1157051#M286180

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703