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 Down to first Decimal

JulianT
5 - Atom

Hello,


I'm trying to round my data down to the first decimal. My data looks like the below:

 

UserValueDesired Result
11.231.2
21.251.2
31.271.2

 

I've tried using the floor function, but that rounds to the nearest integer. The round function also does not give me my desired result as it round based on 0.5.

 

Thanks!
Julian

7 REPLIES 7
StephenR
Alteryx
Alteryx

I would convert it to a string and take Left([Value], 3), then you can convert it back to a number.

Regards,
Stephen Ruhl
Principal Customer Support Engineer

myastarling
10 - Fireball

The other thing you can do is multiply it by 10, take the floor, and then divide it by 10. That way, if the decimal goes out to additional places, then it will still round to tenths.

 

(floor([Field2]*10))/10

ponraj
13 - Pulsar

I would recommend you to use select tool with configuration set to Type:Fixed decimal and Size 19.1

mbooker
6 - Meteoroid

I found that (floor([Field2]*10))/10 worked correctly but  "Type:Fixed decimal and Size 19.1" actually rounded up 1 item in my data set!

mbooker
6 - Meteoroid

then I found that the floor method converted a different value incorrectly

 

The value was 38.55

i was trying to round down to 2 dp

(note that some other values had 3 dp to start with E.g. 27.595

 

The data came into Alteryx from Excel as a double. the value in the Excel cell was exactly 38.55.

 

Upon further investigation the basic formula (the second one) appears to confirm the issue, perhaps as a bug. I'll log this with support

 

 

 

mbooker
6 - Meteoroid

This is what Alteryx Support said. Important information.

 

I spoke with one of our Principal Engineers and he told me that this is expected behavior because Alteryx converts all numbers to a Double when doing math operations and then converts them back to the original data type. This can result in a loss of precision due to how computers store floating point numbers.

 

The number 38.55 is 38.5499999999999971578290569595992565155029296875 to a computer.

 

When you multiply this by 100, the number becomes 3854.999999.... and the floor function then converts it to 3854.

 

A way to avoid this would be to round your number to two decimal places first. I recommend changing the formula to below:
FLOOR(Round( 3355 , .01) * 100 )

 

Rounding to two decimal places first (or in some cases rounding to 4 decimal places and then rounding again to 2 decimal places) is recommended for operations in financial math.

 

 

ChrisTX
15 - Aurora
Labels