Error in Decimal subtraction

Can a LOOKUP formula introduce errors in decimals?
The formula deduces from OnHand column the sold Quantity stored in Sales Table of each Item.
All the integer results are perfect, but decimals are not subtracting correctly. Usually the result of this subtraction is larger so I wonder if LOOKUP is rounding up?

([OnHand] - LOOKUP([_THISROW].[Item],SalesTable,Item,Quantity))

Could you share the data types for

[OnHand]

[Quantity]

fields?

I assumed both fields are currently set to Number type so the result is always number, cut off the decimals.

Hi,
Both are decimal

@Steve

I need Steve to coach us for this kinda subject.
This kinda of Appsheet calculation will not bring the result which we learn at the school.

1 Like

It is a strange behavior, the items that counted in integers have no problems and the subtraction results are always correct. The problem arrives when an item is entered in decimals.

Also I should note that the operation is initiated using the multiple item selection box on the top right side. I select 30 to 60 items at a time to do the subtraction line by line.
Thanks

Do you have a sample with a result?

1 Like

Hi,
I will prepare some same numbers with the results tonight.
Thanks you,

Is it possible there is a mismatch in the Decimal digits setting of the two Decimal columns?

Thanks Steve, the related items are in two decimals. That is the inventory table quantity is with two decimals, and the table that contains the items to be subtracted has two decimals.

1 Like

I have the example right out of the APP.

The beginning inventory table:


Bodega is where the OnHand quantity is shown.
The table that has the items to be deducted:

The APP screen when I select the items to be subtracted:

The ending inventory table:

If you do the math:
Beginning inventory “Medidor Saga” is 4
Subtracting Qty of 3 from the orders
Final Inventory = 1
This shows that the integer items work properly.

Beginning inventory “Tubo 1/2…” is 19.3
Subtracting Qty of 3 from the orders = 0.2+0.3+0.4 = 0.9
Final Inventory should be = 19.3 - 0.9 = 18.4
But it shows final inventory of = 18.7
This shows that the decimal subtraction is wrong.

The other item: “Cemento Holcim”
Also gives wrong results.

Finally I record the changes item by item on confirmationtable shown below:


This shows that the items subtracted are correct and in the correct quantities.

THE ACTIONS:
The multiple action that records the transaction and then substracts the items:

The subtracting action:

Thanks for your patience and help!

In your table images i do not see a column by the name = “Item”. Were the names changed for the purpose of the post? If so, could you post the ATUAL formula you are using. It is important to know what column you are using to do the LOOKUP().

1 Like

Yes, the table is in spanish.
Item = Partes
Quantity = Cantidad.
OnHand = Bodega

The problem then is that you are not linking to the correct row to get its Quantity value.

For example you have several rows with Partes column = “Tubo 1/2X6 mts Rival”. Which one is it suppose to pick up? I am not sure how LOOKUP() performs in this situation (@Steve??) but i suspect it just returns the first one it finds in the table search.

You need to link the rows by ID.

1 Like

Ok, Partes is a unique Item and there are no identical Items so I do not use as uniqueid()

Inventory table:

Table to subtract from inventory:

The table to subtract has the Item as ref to the Inventory table.

Also I works without errors on the integer items.

It is unique in Inventory, but it is not unique in your Sales table…correct. In other word, In SalesTable you have multiple rows with the same Item listed.

So lets back up a bit. When you execute this expression below, you are doing this from the Inventory table…correct? [_THISROW] is an Inventory Table row?

([OnHand] - LOOKUP([_THISROW].[Item],SalesTable,Item,Quantity))

1 Like

@RenatoE

Actually, I just re-read this. Your intention is to SUM all of the SalesTable rows where Item = “Tubo 1/2…”.

Do I have that right now?

If so, you can’t use LOOKUP() for that. You’ll need to do a SUM(SELECT()) expression.

1 Like

@RenatoE

The expression should be something like:

([OnHand] - SUM(SELECT(SalesTable[Quantity], [Item] = [_THISROW].[Item]))

1 Like

The APP is not adding the sales table. It is processing item by item and also deducting from inventory item by item until the entire table is processed.

It is important to note that the system is working for all items in integer form. It is only failing to process the decimals correctly.

I may consider doing this sum on the next iteration. I was unable to make that work so I implemented a line item by line item using the checkbox on the Sales Table as shown below: