Aggregate.Sum calculations contain floating point precision issue

Aggregate.Sum calculations contain floating point precision issue

Posted by: gibbonsk on 5 March 2021, 12:39 pm EST

• 

  gibbonsk

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 5 March 2021, 12:39 pm EST

  It appears that the Aggregate.Sum calculations have a floating point precision problem. Please see this demo that I created to reproduce the issue: https://stackblitz.com/edit/wijmo-angular-reibi8

  Please note the “Sales” column that has an aggregate value of “-0.00”. The four values in the column (9000000 + 163020.93 - 9163037.91 + 16.98) should equal zero (0), but the value being calculated by the Aggregate.Sum function is -4.47034409489788E-10. You can see this by exporting the grid and viewing the value in the resulting Excel spreadsheet.

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 8 March 2021, 1:56 am EST

  Hi Kalen,

  This is an expected issue. The default format of number columns is n2. So, if we will format this value according to n2, then the result will be -0.00 because the value has more than 10 decimal places.
  
  To resolve this issue, you can either set the format of the complete column to e or n15 or you can use the formatItem event to only format the footer of the column.

  Please refer to the sample link below for reference:

  https://stackblitz.com/edit/wijmo-angular-5ucmna

  Regards,
  
  Ashwin

• 

  gibbonsk

  • Post Options:
  • Link
    Copy

  Posted 10 March 2021, 7:42 pm EST

  Hi Ashwin,

  Thank you for your response but I feel like your missing the issue here. I understand that by changing the column format that I can adjust the number of decimal places shown. However, the real problem is that the value should be zero (0), not -0.0000000004470344.

  Using basic math: (9163037.91 - 9163037.91) should equal zero. The -4.470344e-10 value displaying the grid is likely a JavaScript floating point precision error that should be corrected.

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 11 March 2021, 2:30 am EST

  Hi,

  Sorry, I misunderstood your issue.

  This is a technical limitation of Javascript. So, it would not be possible for us to fix it. Even the google calculator returns the same result instead of 0.
  
  I also checked with some math libraries for JS like math.js and numbers.js but they also have the same issue.

  ~regards

• 

  gibbonsk

  • Post Options:
  • Link
    Copy

  Posted 11 March 2021, 1:27 pm EST

  Ashwin,

  I understand the technical limitation of JavaScript and floating point precision issues (I put that in the title). But most people round their mathematic operations to avoid such precision errors.

  What Wijmo should be doing, is rounding the Aggregate.Sum result to match the the number of decimal places specified by the column format.

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 12 March 2021, 1:27 am EST

  As per the exported excel file, the result should be 0.00 instead of -0.00. I have forwarded a bug report to the developers regarding this with internal tracking id WJM-19543. The resulting value will be the same as excel.

  I will update you once I will hear from the devs.

  ~regards

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 16 March 2021, 6:22 am EST

  Hi Kalen,

  The issue is fixed in the latest RC build and will be added to the next release. You can verify the same using the sample below:

  https://stackblitz.com/edit/typescript-zw5dwy?file=index.html

  ~regards

• 

  gibbonsk

  • Post Options:
  • Link
    Copy

  Posted 16 March 2021, 11:28 am EST

  That looks better, thanks for the fix Ashwin.