Good morning community, I have the following problem, I have this data:
| CODE | GUY | DATE | QTY | WITH |
| 5269 | II | 20/5/2022 | 50 | 5,00 |
| 5269 | COMP | 21/5/2022 | 100 | 5,20 |
| 5269 | VTA | 22/5/2022 | 80 | - |
| 5269 | COMP | 23/5/2022 | 80 | 5,60 |
| 5269 | AJE | 24/5/2022 | 5 | - |
| 5269 | VTA | 25/5/2022 | 8 | - |
| 5269 | VTA | 26/5/2022 | 15 | - |
The Column "Code" represents an item of the warehouse,
The "Type" column is classified ("II" = Initial Inventory, "COMP" = Purchases, "VTA" = Sales", "AJE" = Adjustments)
Every time there are Types "II" and "COMP" the stock is increased (Suman), When there are "Types "VTA" and "AJE" there are stock reductions (Subtract).
It turns out that I must calculate the total value that each operation has, however when there is one of Type "II" or "COMP" it is simply multiply the Column "QTY" * "CU", now, when arriving at the Type "VTA" or "AJE" this must be the Average Value of the previous operations.
Currently I have other columns that calculate the "Accumulated QTY" by "Code" and the "Total Value" which is to multiply "QTY" * "CU".
| CODE | GUY | DATE | QTY | WITH | QTY ACUMULADA | TOTAL VALUE |
| 5269 | II | 20/5/2022 | 50 | 5,00 | 50 | 250,00 |
| 5269 | COMP | 21/5/2022 | 100 | 5,20 | 150 | 520,00 |
| 5269 | VTA | 22/5/2022 | 80 | - | 70 | - |
| 5269 | COMP | 23/5/2022 | 80 | 5,60 | 150 | 448,00 |
| 5269 | AJE | 24/5/2022 | 5 | - | 145 | - |
| 5269 | VTA | 25/5/2022 | 8 | - | 137 | - |
| 5269 | VTA | 26/5/2022 | 15 | - | 122 | - |
The problem arises when calculating the "CU" of the "VTA" and "AJE" Types since these must be the average CU of the previous operations; which must be calculated "Total Value" / "Accumulated QTY", and generates an average that is saved and accumulated, however the system throws me that there is a "Circular Dependency" for it.
Ideally, the result should be as follows:
CODE | GUY | DATE | QTY | WITH | QTY ACUMULADA | TOTAL VALUE | WITH PROM | TOTAL |
| 5269 | II | 20/5/2022 | 50 | 5,00 | 50 | 250,00 | 5,0 | 250,0 |
| 5269 | COMP | 21/5/2022 | 100 | 5,20 | 150 | 520,00 | 5,1 | 770,0 |
| 5269 | VTA | 22/5/2022 | 80 | - | 70 | - | 5,1 | 359,3 |
| 5269 | COMP | 23/5/2022 | 80 | 5,60 | 150 | 448,00 | 5,4 | 807,3 |
| 5269 | AJE | 24/5/2022 | 5 | - | 145 | - | 5,4 | 780,4 |
| 5269 | VTA | 25/5/2022 | 8 | - | 137 | - | 5,4 | 737,4 |
| 5269 | VTA | 26/5/2022 | 15 | - | 122 | - | 5,4 | 656,6 |
What I want to validate is whether you can have a table with calculated columns that determine the value of the average inventory per transaction according to the different Imputs that are included.
I hope you can help me and grateful with any support.
Best regards!
