Hi everyone first time poster here I usually try and search up my own answers, but i've come across a problem that I haven't been able to solve yet and hoping for some help here.
Essentially i'm trying to calculate whether a product has sufficient stock in inventory in a load script, it needs to be part of the load script as the result of this calculation (sufficient stock, insufficient stock) will be used as dimensions for analysis.
As a watered down example, an input table (as a csv) is of the format:
Allocated Batch Num
Note, total inventory comes in on every order line, but is specific to a product, so there's 250 units of apple in the total inventory.
If a order has an allocated batch number, then it's considered allocated.
Orders are sorted by date.
The new calculated field will be called 'Achievable', and values will be 'Sufficient Stock' and 'Insufficient Stock'
By default, anything that has been allocated is achievable.
For anything else, the order is acheivable if:
the total inventory minus total allocated stock minus all unallocated orders for that item that occur before the current order is greater than the order quantity of that line.
Some examples to explain the logic:
For Order 10005:
Product = Apple
Order Qty = 95
Total Inventory of Apple = 250
Total Allocated Apple = 143
Total unallocated orders of apple that come before order 10005 = 100
Effectively, there's 250 - 143 - 100 = 7 apple available, and therefore order 10005 cannot be fufilled.
For order 10004:
Product = Grape
Order Qty = 35
Total inventory of grape = 185
Total allocated grape = 100
Total unallocated orders for grape that come before order 10004 = 0
Effectively there's 185 - 100 - 0 = 85 grape available, therefore order 10004 can be fufilled.
I hope i posted up enough information for someone to understand what i'm trying to achieve.
I have tried grouping, however that only gives me the total. Which is fine for totalling allocated stock, but I haven't been successful in getting it to do the cumulative total of orders for a product that preceed this order. I suspect some sort of range sum may be the answer, but I'm inexperienced in using that function.