Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEAThis one was quite fun! Great little brain teaser here thanks @JoeM
Having solved it this way ... I wonder if there's a more Pythonic way to do this?
Because isn't this a Linear Programming problem?
This warrants more investigation!
UPDATE: also! I was reading through the "Make Group" tool mastery entry and realised that the "Make Group" tool might've been able to simplify my workflow by not requiring. But still needs further meditation.
This one has 3 steps, Prep it! Ship it! and Report it!
1. Prep it
First we need to construct our view of inventory, Joined to the orders. 3 tools and done, I bet you my Excel buddies are jealous!
2. Ship it
Figure out which of the orders we are going to fulfil, I'm using a Boolean flag, initially set all to "True" and then a series of Multi-row formula to solve.
I had to synthesise an index by joining the text of the Order SKU and the Order quantity. Sometimes when you can't solve an analytics challenge with the raw data, you need to build a scaffold and synthesise the data you need.
If I had the facility to "Group by" the SKU's and the Order ID's in the Multi-row Formula tool, I wouldn't need the scaffold.
3. Report it
Now we filter out the rows that we don't want,
and build a cumulative consumption column.
For this one, I output the results to Excel cos I wanted to compare my results with the solution's results as I had messed up one of the formula's earlier which is below here. The first time I did this I didn't make the conditions mutually exclusive / collectively exhaustive (MECE) enough, because I had a <= symbol instead of a < symbol. Small things! I tell ya!
IF
[Order Quantity] < [Quantity in Stock] AND [Row-1:Order SKU]!=[Order SKU]
THEN
-[Order Quantity]
ELSEIF
[Row-1:Drawdown]-[Order Quantity]+[Quantity in Stock] < 0 AND [Row-1:Batch]=[Batch]
THEN
[Row-1:Drawdown]
ELSEIF
[Row-1:Drawdown]-[Order Quantity]+[Quantity in Stock] >= 0
THEN
[Row-1:Drawdown]-[Order Quantity]
ELSE
[Row-1:Drawdown]
ENDIF
This one nearly broke me. Multi-Row is definitely a bit of a blind spot--spent way too long trying to come up with a solution that didn't use it.
Solution:
Not sure if anyone else has done it like this, but managed with just one formula. Tripled checked results and seems ok.
if [Order SKU]!=[Row-1:Order SKU] then [Supply]-[Demand]
elseif [Order SKU]=[Row-1:Order SKU] and [Row-1:Remaining]-[Demand]>0 and [Row-1:Remaining]>[Demand]then [Row-1:Remaining]-[Demand]
elseif [Order SKU]=[Row-1:Order SKU] and [Row-1:Remaining]-[Demand]<0 then [Row-1:Remaining]
else [Row-1:Remaining]-[Demand] endif