Hello KNIME community!
Please, I am stuck trying to solve the following problem: I have a dataset that is chronologically sorted and each line represents one entry or exit to my inventory for a given SKU in a given invoice. Eventually, the same SKU will appear more than once and I need to update its balance based on the quantity sold/bought. Currently, I managed to prepare my dataset in KNIME with the first 6 columns, but I can’t replicate the last two. Column Quantity represents the amount sold/bought for a given SKU in a given invoice. Entry_cost represents the unit cost that I had for the items that I bought (they are zeroed when it is a sale). Previous_month_balance represents the quantity that I had in my inventory for a given SKU at the end of the previous period. Previous_Month_Cost is the same logic but for the unit cost that I had at the end of the previous period.
The two columns that I am trying to create have the following logic: Updated_balance: It should look up for the previous balance and update it based on the amount bought/sold, Example: SKU A111, we had 4 units at the end of the previous period, we sold 1 unit so the updated_balance should be 3. Later, we sold another 2 units, so the updated_balance should be 1. Finally, we bought 10 units, so the updated balance should be 11. It is important to factor in the chronological order of the invoices as I will need it to calculate the last column (Updated_cost). Here, I need to calculate (simple average) the unit cost, whenever I buy a given quantity of a SKU. Example: SKU A111: When I bought 10 units of it, with a unit cost of 684, I had only 1 unit of the same SKU in my inventory with a unit cost of 564, so the new unit cost should be (10684+1564)/11 = 673,09
Thanks for the attention!
