Inconsitancy by data aggregation

There is a known issue with number precision...

https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/managing-precision-in-number-field-seeing-non-exact-values.-5dbeed16466081700bd68940

If you increased the precision you might see it. Do you need more than 2 decimal places?

It looks like Ninox isn't the only one with this issue...

https://cloud.google.com/spanner/docs/storing-numeric-data

Well, it looks like I stepped on it again. First, I took more time to read the Google doc that I posted a link for and they do have a data type that can store numbers correctly, NUMERIC. I focused on the FLOAT64 data type and that matched what was going on in the threads I posted the link for. Second, I went back to the old Ninox threads and tried to duplicate the issues and couldn't. It appears that they have resolved number storage problem.

Ilde, I'm not sure why you are using the number() function when you are already dealing with numbers or have already converted to a number. I also don't know if it makes any difference.

Hi Ilde,

I'm not ignoring you, my work schedule won't allow me to look at it until this evening MST. If anyone else has an observation, feel free to jump in.

Did you try with round function ?

let t1 := round(i.'Invoice Amount IC',2)
let t2 := round(total + i.'Delivery Costs IC',2)

Ilde, I would change

let total := sum((select 'Invoice Items'['Invoices' = i]).'Invoice Line Amount IC')

to

let total := sum(i.'Invoice Items'.'Invoice Line Amount IC')

Usually, if a formula works after using the text() function, the format() function also converts to text, it is because the item inside the function is an array. When it's converted to text, you are adding apples to apples instead of apples to oranges.

Indeed, the format you define for a number field only affects the display. Internally, I am pretty sure that Ninox stores all the numbers in binary floating point format (BFP). So, when you type a decimal number in a number field, it is converted to BFP, and then converted back to decimal for displaying it. The catch is that, as soon as a decimal number has a fractional part, it cannot be represented exactly in BFP, except for a few special cases. So, a decimal number with a fractional part is, most of the time, encoded as a BFP number that is very (very very) close, but not equal, to the original value. The encoding and decoding routines are generally well done, so that the number displayed is usually undistinguishable from the original.
The matter becomes more complex when you start to make some computations, like for example a sum. There, the little differences start to accumulate. You do get a BFP number that is very close to the true sum and, when this result is displayed, it will generally looks correct. But if you encode a decimal representation of the result in another field, there is a fair chance that the BFP produced by the encoding routine will not be exactly identical to the result of the summation performed in BFP. Hence the spurious differences you encountered. The best solution, since you are only interest in the two first decimals, is to follow Jacques's advice and round the numbers before performing the comparison. The round() function produces its own BFP encoding of both rounded numbers, so those BFP encodings are identical if the rounded numbers are equal. 
There have been computers that did treat the numbers internally as decimal, with each digit separately encoded into binay (BCD), to avoid those problems, especially for administrative and financial tasks. See "Decimal Computer" in Wikipedia.

Hi Alain,

Do you have an example number or calculation that demonstrates that they are using BFP. I tested the numbers that are in the other threads and can't duplicate the problem as it existed then. Hopefully, they are using one of the alternatives mentioned in the Google document.

Unfortunately, it seems difficult to find tests that gives real inside information. It is also quite possible that the various versions of Ninox, running in different environments, make use of different strategies. On the latest iOS version, the two formulas below give different results, false for the first one and true for the second one. This leads to a string suspicion, but admittedly not a proof, that true decimal arithmetic is not in use.

4.57 + 318.35 + 145.98 + 14512.89 = 14981.79
round(4.57 + 318.35 + 145.98 + 14512.89, 2) = round(14981.79, 2)

It's an interesting case that happens frequently in other languages.
To better understand it, it is necessary to know that a floating point number is composed of a mantissa part and an exponent part. The number of bits assigned to the lie allows to know the number of significant digits. The exponent increases the range of the mantissa without improving its precision. See https://en.wikipedia.org/wiki/Double-precision_floating-point_format

In our case, the problem would come from the smallest significant number which would not be at 0. I wanted to know what was the number of sinificative digits of the real numbers of Ninox with this small program:

let V1 := 1;
let V2 := 0.1;
let NbDecimal := 1;

while V1+V2 != 1 do
V2 := V2 / 10;
NbDecimal := NbDecimal + 1;
end;

alert("NbDecimal : "+(NbDecimal-1)+ "
V2 : "+V2*10);
"NbDecimal : "+(NbDecimal-1)+ "
V2 : "+V2*10

The result is :

NbDecimal : 15
V2 : 0.000000000000001

Ninox floating point numbers appear to be 64-bit real numbers.

Ilde, can you display your variables with 15 decimals  to check whether the least significant digits are different from 0 ?

You can try it on Console :
1+0.000000000000001            => 1
(1+0.000000000000001)=1     => No