Aggregate node - incorrect value
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.
-
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
-
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
Please sign in to leave a comment.
Comments
8 comments