Data360 Analyze

Hello,

I am using the aggregate node to calculate the sum of the sales Value, for a specific country (Germany, in the example attached), I get the value incorrect. 

How Can I solve it? I tried the calculate node with function "round", before and after the aggregate node, but I get the same result.

The Version in use is 3.6.0.

Thank you!

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.

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.

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)?

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

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.

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? 

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

Thanks a lot, it works!