Hi
What I am trying to do is for an item and date which are both ascending - I need the earliest date first to apply stock to a row until it hits 0 or whatever is remaining
I need to subtract sum_qty (which is my available stock) from order quantity (which is the order quantity) which gives me the stock for that item and if remaining greater than sum_qty just apply whatever is remaining in the order quantity meaning I have none left to apply to the next line if the item is the same as previous row
I have several items the same so the total only applies to the same item, if the next row is a different item start the subtraction again
I have some data here as follows:
| SKU | Sum_QTY | Order Quantity | Expected Outcome | Date |
| B6967Q8C8585E0 | 3 | 3 | | 1/5/2022 |
| B7403Q7C8114W0 | 10 | 14 | | 11/16/2021 |
| B7403Q7C8114W0 | 10 | 14 | | 11/23/2021 |
| B7671Q8C8LB5E0 | 6 | 1 | | 11/30/2021 |
| B7671Q8C8LB5E0 | 6 | 2 | | 12/10/2021 |
| B7671Q8C8LB5E0 | 6 | 10 | | 12/15/2021 |
| B7671Q8C8LB5E0 | 6 | 4 | | 1/5/2022 |
| B7671Q8C8LB5E0 | 6 | 5 | | 1/11/2022 |
| B7671Q8C8LB5E0 | 6 | 5 | | 1/28/2022 |
| B7672Q8C8WL5E0 | 4 | 2 | | 12/14/2021 |
| B7672Q8C8WL5E0 | 4 | 2 | | 1/21/2022 |
| | | | | |
What I am trying to achieve is the following:
| SKU | Sum_QTY | Ord Qty | Remaining | Stock for Order | Date |
| B6967Q8C8585E0 | 3 | 3 | 0 | 3 | 1/5/2022 |
| B7403Q7C8114W0 | 10 | 14 | 0 | 10 | 11/16/2021 |
| B7403Q7C8114W0 | 10 | 14 | 0 | 0 | 11/23/2021 |
| B7671Q8C8LB5E0 | 6 | 1 | 5 | 1 | 11/30/2021 |
| B7671Q8C8LB5E0 | 6 | 2 | 3 | 2 | 12/10/2021 |
| B7671Q8C8LB5E0 | 6 | 10 | 0 | 3 | 12/15/2021 |
| B7671Q8C8LB5E0 | 6 | 4 | 0 | 0 | 1/5/2022 |
| B7671Q8C8LB5E0 | 6 | 5 | 0 | 0 | 1/11/2022 |
| B7671Q8C8LB5E0 | 6 | 5 | 0 | 0 | 1/28/2022 |
| B7672Q8C8WL5E0 | 4 | 2 | 2 | 2 | 12/14/2021 |
| B7672Q8C8WL5E0 | 4 | 2 | 0 | 2 | 1/21/2022 |
Hope that makes sense - and thanks in advance for any help!!
