SOLVED - Bizarre Calc Element Results

[nclaypool]

I have a calc element that performs a DB pull of a repeating group and sums up the totals from each item then subtracts that from a grand total, this is used to show that all dollar amounts were accounted for allowing the record to be closed for processing. This seems to work flawlessly except for one record that was entered today.

Somehow the calc result ended up being: $-2.2737367544323E-13

The repeating group is comprised of 10 records with varying totals. For testing I deleted a row, the calc result came out correct. I then added the row back in but at one dollar less than it had originally, the result should have been 1 but instead I got .99999999977 or something close to that.

Here's the code that runs the calc element:

$dr_id = {deposit_records___id};

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
  ->select('total')
  ->from('deposit_records_66_repeat')
  ->where('parent_id = ' . $dr_id);
 
$db->setQuery($query);
$calc_ledger = $db->loadColumn();

$total = array_sum($calc_ledger);
$dr_deposit_total = (float){deposit_records___deposit_total};
$grand_total = $dr_deposit_total - (float)$total;

return $grand_total;

I played with this a little bit, having the field return different variable values and confirmed that the summing of the array from the database pull is creating accurate totals and the deposit total being pulled from the record is accurate as well. Something bizarre is happening at the point where the grand total is calculated.

 

[cheesegrits]

It's a floating point thing. A lot of floating point numbers can't actually be exactly represented in binary. So a number that can easily be represented in base 10, like 0.1, only has an approximation in binary, and any "non-terminating" number in binary (the binary version of "recurring") will yield approximate results. So 0.1 in binary is a non-terminating 0.1100110011001100 (etc ad infinitum).

http://php.net/manual/en/language.types.float.php

The usual fix is to round() your result.

Or multiply everything by 100, do integer math, then divide by 100.

-- hugh

 

[nclaypool]

It's a floating point thing. A lot of floating point numbers can't actually be exactly represented in binary. So a number that can easily be represented in base 10, like 0.1, only has an approximation in binary, and any "non-terminating" number in binary (the binary version of "recurring") will yield approximate results. So 0.1 in binary is a non-terminating 0.1100110011001100 (etc ad infinitum).

http://php.net/manual/en/language.types.float.php

The usual fix is to round() your result.

Or multiply everything by 100, do integer math, then divide by 100.

-- hugh

A suggested fix and I learned something about PHP and math. Thanks a bunch hugh.

 

[cheesegrits]

Or, as Bender would say ...

-- hugh