We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Aggregate node - incorrect value

Comments

8 comments

  • Avatar
    Adrian Williams

    Can you clarify what you mean when you say you get the incorrect value?

    Do you mean the value is different to that expected (i.e, a rounding error) or that the displayed value in the data viewer is in exponential format?

    If it is the latter, there is no option to set the format of floating point (double) displayed value and very large or small values will be automatically displayed in the exponential format.

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    If the issue relates to the cummulative effect of rounding errors when processing floating point (double type) numbers then you may find the reply to this post on floating point numbers to be of interest.

     

    0
    Comment actions Permalink
  • Avatar
    annunziato lo bosco

    Hello,

    thanks, I will check the post.

    yes, the displayed value in the data viewer is in exponential format. Is it possible to add, in the script, a fuction like round(field,2)?

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    You can use a custom aggregate function in the Advanced tab of the Aggregate node's 'Operations' property.

    In this example the 'IOU' field is rounded before the values are summed:

     

    See the 'Advanced use of grouping funcions' section of the Python module support Help topic 

    https://doc.infogixsaas.com/analyze/Default.htm#f-script/python/python-modules.htm#Advanced

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    Note that even though the values are being rounded to 2 decimal points, the values are still floating point numbers so you may still obtain inaccuracies when the rounded values cannot be exactly represented as a floating point number.

    0
    Comment actions Permalink
  • Avatar
    annunziato lo bosco

    I tested the solution you proposed with the values rounded to 2 decimal points. I still obtain inaccuracies. Please, is there any better solution to eliminate these inaccuracies? 

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    Just to clarify the results shown in the Data Viewer, the value 1.147857619E7 is the same value as 11478576.19  the only difference is the display format. The Data Viewer will always display large floating point (double type) numbers in this way.  You can confirm the underlying number is the same regardless of the display format by outputting the value to an Excel spreadsheet, e.g. 

    If the values you want to sum are currency values with a consistent number of decimal places for all values e.g. 2 D.P. for cents, then you can convert the floating point currency values to the equivalent Cents value. The value can be stored as a long data type and be summed in the Aggregate node. For example, using the following test data in a Create Data node:

    It would display in the Data Viewer as:

     

    A Calculate Fields node could be used to calculate the SalesValue in terms of Cents:

    Which would result in the following:

    The Aggreagate node can then be used to sum the Cents value:

    Resulting in the following totoal value in Cents:

     

    If your data is currency values to 4 D.P. the expression in the calculate fields would need to be changed to retain all the significant digits.

    See the attached example data flow.

     

     

    Attached files

    Aggregate_Cents_Values - 16 Jun 2020.lna

     

    0
    Comment actions Permalink
  • Avatar
    annunziato lo bosco

    Thanks a lot, it works!

    0
    Comment actions Permalink

Please sign in to leave a comment.