Hello All,
I'm attempting to do expiration calculation on lot codes based on a few points
QTY in the lot
Sell by Date
Days on Hand
When importing the inventory, we add 1,2,3 etc... to each line and use that as the code for the list. So we import the codes into the list first, then in the module it would look something like this (with a lot more data)
1 = SKU A, BATCH 123
2 = SKU A, BATCH 456
3 = SKU B, BATCH 654
I also have another list that uses the lot code as the code which I use for other modules.
Now when I do this in excel, it's a complex formula but it's doable. Basically, I sort by SKU and then sell by date. Then the formula is something like
IF row above is the not same SKU as current row, then QTY on hand divided by average daily forecast demand for next 6 months (this gets days on hand)
Days on hand plus today's date = Sell through Date
IF sell through date is less than sell by date, 0 goes short dated, IF sell through date is greater than sell by date then (Sell through date - Sell by date) * average daily forecasted demand
IF row above is the same SKU, then same DOH calculation based on qty in lot, plus the sell through date of the row above
The issue that I'm running up against is in Anaplan you can compare line items but I don't know how to compare list items. I saw SOME documentation around this but it seemed kind of murky.
There's the ISFIRSTOCCURANCE which could potentially work, but it looks like the calculation only has 1 line item to look up against and I would need 2. I guess I could just use AND. However, I'd still need to add onto the sell through date on the above list item. Any thoughts on how to do that?
