Subtraction precision between two floating point values

[jpscoralick]

Hello everyone!

I'm working with big floating point numbers as 369814.705617128, for example (see attached file below).

I need to store the subtraction [X] - [Y] (both are Double type) with two decimal places in the [Z] field (Fixed Decimal type with 19.2).
I've made three tests to check the result, as it follows:

1) [X], [Y] and [Z] as Double type.

2) [X] and [Y] as Double Type and [Z] as Fixed Decimal with 19.2

3) [X] and [Y] converted to Fixed Decimal with 19.2 and [Z] as Fixed Decimal with 19.2

So, only on the first case I'm getting the CHECK field with True.

Once I need to store [Z] with two decimal places, how can I properly get [Z] with the exact value from the subtraction [X] - [Y]?

Thanks in advance!

[DataNath]

Hey @jpscoralick, taken from the Alteryx help page on data types - https://help.alteryx.com/20221/designer/data-types - 

So it looks like, although your [X] and [Y] fields are FixedDecimals, when used within a Formula tool they're converted to doubles for the calculation, hence why the level of precision doesn't match and the [CHECK] returns false, despite them visually appearing the same on the surface. We can see this when we make [X] and [Y] FixedDecimals and [Z] a double, this returns 'True' although visually it ought to be False as the level of precision is totally different:

Likewise, if we keep all 3 fields as FixedDecimals, but apply some rounding within the calculation itself, we can also return a 'True' value as we bring the 2 values (the result of [X]-[Y] and [Z]) to the same level of precision i.e. 2 decimal places:

[gyang3]

@jpscoralick this article is fairly informative and should help explain why you are getting the results you are seeing in your screenshot

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Floating-point-numbers-are-surprisi...

[jpscoralick]

Thanks a lot, @DataNath e @gyang3, for the quick and helpul answers!

So, to summarize it all: there's no useful way to represent a result as a Fixed Decimal with 100% of precision? Will I always lose some information during all the process?

[DataNath]

@jpscoralick if you want to display your result as anything less than the raw calculation of [X]-[Y] I.e. to 2 decimals as you mentioned before then by default you’ll lose the level of precision yes, as the reduction in decimal places has to either come from rounding or truncation, depending on the desired output.