Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Rounding problems

SAPaul
8 - Asteroid

Hi All,

the rounding tool doesn't seem to be rounding in the conventional way

 

i used round([year 2 avg],0.1) this is fine for all except row 2. Is there any round this?

 

SAPaul_1-1573122944695.png

5 REPLIES 5
Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @SAPaul,

 

The reason for this is there can be slight variations in the way computers store these double data types due to binary conversions.

 

From an earlier post i've read previously (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Alteryx-Odd-Behavior/td-p/89086)

For example:

206.95 when stored as a double in binary is actually '206.94999999999998863131622783839702606201171875' and when rounded at the first decimal place becomes 206.9

1.45 when stored as a double is actually '1.4499999999999999555910790149937383830547332763671875' which when rounded to 1dp becomes 1.4

1.55 when stored as a double is '1.5500000000000000444089209850062616169452667236328125' which rounds to 1.6 at 1dp

 

Taking a look at an example we see the true way these numbers are being stored:

 

image.png

The way round this would be to round to 1 extra decimal place before rounding to the level of precision you are looking for. In this case to round to 1 decimal place we would need to round to 2 decimal places and round to 1 decimal place on this new value.

 

Round(Round([Number], 0.01), 0.1)

 

This would result in the value you're looking for:

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workbook for you to download if needed.

 

Regards,

Jonathan

Julien_B
8 - Asteroid

You can try this workaround : round([year 2 avg]*10,1)/10

Julien_B_0-1573126501416.png
Seems to work on my sample data 🙂 

SAPaul
8 - Asteroid

I tried to apply this with a multi-field formula but it creates another anomaly (see highlighted cells) 

 

SAPaul_2-1573128717883.png

 

Julien_B
8 - Asteroid

the jonathan's  solution may work better tan mine...

ChrisTX
15 - Aurora
Labels