I’m curious if anyone has any ideas on how to solve the problem in the picture below. We want to try and calculate future inventory based on the projected sales and previous inventory but not allow for negative inventory and essentially ignore projected sales when there is no inventory.
The data is stored in Redshift and we’ve tried to use the SUM Window function but this fails when you potentially have multiple occurrences of stock-outs as the SUM Window doesn’t calculate inventory position correctly when new inventory takes it above zero.
Here is the Excel formula (Column F) for the running inventory:
F3 = B3+C3-D3 (this is the first row of running inventory)
F4 = IF(F3+C4-D4<0,0,F3+C4-D4) - for all subsequent rows this is the formula. The challenge is that it references the prior row result