0

Inconsitancy by data aggregation

Hello,

Ninox newbie here so please be gentle :) I have imported a lot of a data and want to clean out data inconsistancies.  This case is fairly simple, for each invoice the invoice amount must matche the sum of its invoice items amount plus the invoice delivery costs.

The problem I am having is that "if" in the statement below, for some invoices it displays differences, but when I print these out the values are the same? I have tried comparing these as numbers or text, as many decimals as possible, you name it ... and I don't have consistant results, its driving me crazy.  

The following is the code which I run from the console:

for i in (select Invoices['Auction House'.name = "eBay"] order by 'Purchase Date') do
  let total := sum((select 'Invoice Items'['Invoices' = i]).'Invoice Line Amount IC')

  let c := number(total + i.'Delivery Costs IC')
  if number(i.'Invoice Amount IC') != number(c) then
    format(number(i.'Invoice Amount IC'),"0.0000000") + " != " + format(number(c),"0.000000")
  end
end

Which outputs the following (incorrect) lines:

40.9400000 != 40.940000
88.9800000 != 88.980000

Can anyone explain this behaviour?

Thanks,

Ilde

15 replies

null
    • Sean
    • 3 yrs ago
    • Reported - view

    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?

    • Sean
    • 3 yrs ago
    • Reported - view

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

     

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

    • Sean
    • 3 yrs ago
    • Reported - view

    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.

    • Ilde
    • 3 yrs ago
    • Reported - view

    Hi Sean, thank you for reply.  Yes indeed the number() and format() are unncesary, I was trying to determine where the issue could be.  I only need 2 digits, but I expended to 5 to see if the precision was actually the problem.  The fields are defined as numeric in the table, the code on its bear minimum should be:

    for i in (select Invoices['Auction House'.name = "eBay"] order by 'Purchase Date') do
    let total := sum((select 'Invoice Items'['Invoices' = i]).'Invoice Line Amount IC')

    let t1 := i.'Invoice Amount IC'
      let t2 := total + i.'Delivery Costs IC'

      if t1 != t2 then
       t1 + " != " + t2
      end
    end

    Which yields the following (incorrect) result:

    60.16 != 62.16
    173.98 != 187.97
    135.98 != 135.98
    22.95 != 27.95
    71.35 != 78.35
    40.99 != 40.99
    45.97 != 50.97
    32.95 != 30.00
    40.94 != 40.94
    88.98 != 88.98

    Next I tried with the following:

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

     

    Which outputs (correctly):

    60.16 != 62.16
    173.98 != 187.97
    22.95 != 27.95
    71.35 != 78.35
    45.97 != 50.97
    32.95 != 30.00

    And then tried the following:

    let t1 := format(i.'Invoice Amount IC',"0.00")
    let t2 := format(total + i.'Delivery Costs IC',"0.00")

    Which outputs the same as the previous (correctly):

    60.16 != 62.16
    173.98 != 187.97
    22.95 != 27.95
    71.35 != 78.35
    45.97 != 50.97
    32.95 != 30.00

    What do you think?

    Thanks,

    Ilde

    • Sean
    • 3 yrs ago
    • Reported - view

    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.

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    Did you try with round function ?

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

    • Sean
    • 3 yrs ago
    • Reported - view

    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.

    • Ilde
    • 3 yrs ago
    • Reported - view

    Hi Jacques,

    Thanks for the hint, I tried that one and it also delivers correct result.  Since I am converting amounts between currencies, I am guessing some numbers are stored in the DB with huge amount of decimals and therefore I have the strange behaviour.  Which leads me to believe that the format I indicate in the field (for example: #,##0.00) is for data display only and internally the complete number with full precision is stored.  I will need to rounder the numbers upon saving.

    Thanks for your support!

    • Ilde
    • 3 yrs ago
    • Reported - view

    Hi Sean,

    Thanks a lot! I tried using that syntax initially and couldn't get it to work ... I gave it another go and realized I named the table differently when definining the subtable inside 'Invoices'.  Which of course is very confusing, anyways renamed the subtable as it is called originally and the code works perfectlly.  Thanks a lot!

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    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.

    • Sean
    • 3 yrs ago
    • Reported - view

    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.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    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)

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    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 ?

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

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

    • Ilde
    • 3 yrs ago
    • Reported - view

    Unfortunately I adjust all the code already so the data is rounded upon save :)

Content aside

  • 3 yrs agoLast active
  • 15Replies
  • 1112Views