Why does Atrex use FLOATs to store dollars?

I am using Atrex 19, and have a general question around the calculation of dollar amounts. I'm doing a simple sum on the on the invoice table:

select TransDate as date, sum(SubTotal + Shipping + Tax) as total from invoice group by TransDate;

The results I get are mostly in line with my expectations, except some values end up with way too many digits after the decimal. For example, 14786.019996643066 or 47271.05078125. I took a gander at the table definition and found that the columns in question are all floats. My guess is that the date type, coupled with tax calculations causes the absurd number of digits.

My questions:

1. Why doesn't Atrex us INT or DECIMAL to store financial data instead of FLOAT?
2. Is the user expected to handle the rounding?
3. What type of rounding is used when generating invoices and reports?

Terry Swiers Replied

6/26/2023 at 4:42 PM

Employee Post

> 1. Why doesn't Atrex us INT or DECIMAL to store financial data instead of FLOAT?

We allow for pricing to 5 decimal places. With a signed 32bit integer range of -2147483648 to 2147483647, using that with 5 implied decimal places only gives you a range of 21474.83648 to 21474.83647. The Decimal and numeric implementation in ElevateDB only allows for 4 decimal places, so that's out as well.

> 2. Is the user expected to handle the rounding?

Yes, on the final values.

> 3. What type of rounding is used when generating invoices and reports?

Simple rounding is applied to computed values such as the subtotal, tax, and totals to 2 decimal places. Other values such as price and cost use simple rounding to to the number of decimal places specified in the company settings.

Stan Paulauskas Replied

6/26/2023 at 5:52 PM

I assume that by "simple rounding" you mean "normal rounding" as defined by the Elevate DB manual? From the note on that page:

Also, if using the ROUND function with DOUBLE PRECISION or FLOAT values, it is possible to encounter rounding errors due to the nature of floating-point values and their inability to accurately express certain fractional real numbers.

This is exactly the reason that most financial implementations avoid using FLOAT or DOUBLE for monetary values.

Your answer suggests that the rounding is the last step applied. How do you avoid compounding the FLOAT's inaccuracies as you execute the multiplication and addition operations? Or would I need to do that myself as well?

Terry Swiers Replied

6/26/2023 at 8:24 PM

Employee Post Marked As Answer

> I assume that by "simple rounding" you mean "normal rounding" as defined by the Elevate DB manual? From the note on that page:

Yes.

> Your answer suggests that the rounding is the last step applied. How do you avoid compounding the FLOAT's inaccuracies as you execute the multiplication and addition operations? Or would I need to do that myself as well?

You have to apply rounding at the appropriate and logical locations for the context that you are reporting on.

3 Replies

Reply to Thread

Related Knowledge Base Articles