Rounding Down to first Decimal

[JulianT]

Hello,

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

User	Value	Desired Result
1	1.23	1.2
2	1.25	1.2
3	1.27	1.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

[StephenR]

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

[myastarling · Accepted solution]

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]

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

[mbooker]

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]

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]

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]

This related post is a good read on rounding:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Formula-Round-4-975-0-01-getting-4-97-...