FrontAccounting forum — rounded errors

Re: rounded errors

null@example.com (elax) — Wed, 04 Jan 2012 12:59:01 +0000

I have to change (a while ago) the SQL (not the PHP) in the themes/execlusive/render.php.
That's everything I found so fa

/Elax

Re: rounded errors

null@example.com (joe) — Wed, 04 Jan 2012 12:53:32 +0000

ok, elax, please continue report files and lines that need the new floatcmp filter.

The following files have been updated in the HG repository:
/reporting/rep101.php
/reporting/rep102.php
/reporting/rep201.php
/reporting/rep202.php
/sales/inquiry/customer_inquiry.php

Other files will be added as we find more.

The theme exclusive has been updated for this about a month ago. Please re-install.

/Joe

Re: rounded errors

null@example.com (elax) — Wed, 04 Jan 2012 11:45:16 +0000

joe wrote:

and see if it is ok with the floatcmp.
reporting/rep102.php had alreade a filter in the sql used in the file.
/Joe

This report should be modified too. Even it's filtered by mysql (and I think mysql have the same problem).
array_sum, sum float in php so it has potentialy the problem (Anyway any comparaison of a floating number to 0 as this problem)

That works with floatcmp.

/Elax

Re: rounded errors

null@example.com (joe) — Wed, 04 Jan 2012 11:06:27 +0000

elax, please add the following lines in /includes/current_user.inc after the function number_format2

/* price/float comparision helper to be used in any suspicious place for zero values? 
usage:
if (!floatcmp($value1, $value2)) 
    compare value is 0
*/

define('FLOAT_COMP_DELTA', 0.0000001);

function floatcmp($a, $b)
{
    return $a - $b > FLOAT_COMP_DELTA ? 1 : $b - $a > FLOAT_COMP_DELTA ? -1 : 0;
}

and see if it is ok with the floatcmp.
reporting/rep102.php had alreade a filter in the sql used in the file.

/Joe

Re: rounded errors

null@example.com (elax) — Wed, 04 Jan 2012 09:40:25 +0000

Just search for '== 0' in the code.
So far I found it in 3 files

sales/inquiry/customer_inquiry.php
reporting/rep101.php
reporting/rep102.php

And in the exclusive theme
themes/exclusive/renderer.php

Thanks
/Elax

Re: rounded errors

null@example.com (itronics) — Wed, 04 Jan 2012 09:35:35 +0000

Yes, Elax, you are right . This is really real example . Surely any reported issue of this kind will be fixed.
Janusz

Re: rounded errors

null@example.com (joe) — Wed, 04 Jan 2012 08:11:37 +0000

Thanks elan,

If you have a detailed list of these happenings, I will gladly change the filter.

/Joe

Re: rounded errors

null@example.com (elax) — Wed, 04 Jan 2012 00:10:39 +0000

itronics wrote:

Of course I can be wrong, so we can switch with all the database double columns to numeric, as soon as the problem will be proven on real example .
Janusz

I have an real example , and strangely it's a php problem
echo (335.74+30.52) == (305.22+61.04) ? "equal" : "not equal";
?>

>> not equal.

If you do the same in SQL it's equal (I don't know why).
Anyway I have this real , you can do it yourself on a test database

The invoice is 305.22 + 61.04 of VAT
And I payed with 2 different payments : one of 335.74 and the second one of 30.52 (a credit note)

This payment appears in the list of overdue invoices but not in red. I think the "filtering" is done on the 'sales/inquiry/customer_inquiry.php' line 212.
If you change
(abs($row["TotalAmount"]) - $row["Allocated"] != 0)
by
(abs(abs($row["TotalAmount"]) - $row["Allocated"]) > 1e-6)

everything is fine.

(Even if the column was decimal we will still have the problem as the numbers will be added in PHP)

/Elax

Re: rounded errors

null@example.com (elax) — Wed, 04 Jan 2012 00:08:25 +0000

Of course I can be wrong, so we can switch with all the database double columns to numeric, as soon as the problem will be proven on real example .Janusz

I have an real example , and strangely it's a php problem

Re: rounded errors

null@example.com (joe) — Tue, 03 Jan 2012 23:00:03 +0000

It is not the double precission that causes this problem. Consider the following:

When you pay and allocate customers/suppliers in another currency than the customer/supplier there would occasionally be a small leftover, either positive or negative in the AR/AP ledger. FA does not consider the record as fully paid. That is the reason for doing the check for either 0.0001 or 1e-6 to check that the record is 'paid'.

So I guess we can can continue with double precission.

/Joe

Re: rounded errors

null@example.com (itronics) — Tue, 03 Jan 2012 19:59:17 +0000

Yes, I know this legendary rounding problem, but I couldn't find single place in FA where addition is done so many times without rounding, that the error has a chance to rise above half a cent. Of course I can be wrong, so we can switch with all the database double columns to numeric, as soon as the problem will be proven on real example .
Janusz

Re: rounded errors

null@example.com (joe) — Wed, 28 Dec 2011 21:40:09 +0000

There are some situations, f.i. when you get payments from your customers or pay your suppliers in another currency than the customers/suppliers currency, that you may be left with a small rounding error. This is only located in the receivals / payment ledgers.
This is the reason for using this small rounding fix.

/Joe

Re: rounded errors

null@example.com (elax) — Wed, 28 Dec 2011 11:57:29 +0000

I agree 100 % with you. However the source of the problem is to store number as float (or double) in the database rather than using decimal type.
You should know that floating number can't represent exactly every decimal numbers (0.1 for example is 1/10 and needs an infinite number of digit in base-2) and you can't just compare floating number to 0
The problem appears mostly when you use discount or pay an invoice in many payment, therefore to know if the invoice has been fully paid or not, you just not compare the two amounts but have to compare one amount with the sum of other (the amount paid vs the invoice amount + the discount).

/Elax

Re: rounded errors

null@example.com (janusz) — Wed, 28 Dec 2011 10:54:25 +0000

Well, I'm not sure where the problem appears, but in general case rounding errors should never appear in accounting application. This simply can break balances in some places. Please report where you have found the problem, maybe better approach is just to avoid the problem at the source?

Janusz

Re: rounded errors

null@example.com (joe) — Fri, 16 Dec 2011 21:55:25 +0000

Well I guess the rounding problems is solved by using something like 0.0001 (it has been used in /gl/gl_journal.php.
Or as you suggest 1e-6 is also a good approach. You decide.

I guess you can find my email by pressing my username joe in the left column. You can send the files here.

/Joe