Hi Community, I'm stuck on a problem where I want to update the monthly Inventory for a number of products in the below dataset when the Inventory is below the Inventory Reorder Point in a given month. The Inventory value would be updated in the row that is X amount of months away (X = Lead Time (months) from the first instance the Inventory falls below the Inventory Reorder Point. Below is the example dataset with notes that further explain the problem. ProductDemandLead Time (months)Inventory Reorder PointInventory Reorder QuantityYear MonthInventoryNotesExample Product 12400633526171902023_1139004 Example Product 12400633526171902023_1236248 Example Product 12400633526171902024_0133492Inventory falls below Inventory Reorder PointExample Product 12400633526171902024_0230736 Example Product 12400633526171902024_0327980 Example Product 12400633526171902024_0425224 Example Product 12400633526171902024_0522468 Example Product 12400633526171902024_0619712 Example Product 12400633526171902024_0716956I want the Inventory to be updated here based on what the lead time is ( 6 Months in this instance). The inventory would be updated with the Reorder Quantity: Inventory + Reorder QuantityExample Product 12400633526171902024_0814200The rows afterwards would be updated based on this formula: Previous Row Inventory - Demand. Example Product 12400633526171902024_0911444 Example Product 12400633526171902024_108688 Example Product 12400633526171902024_115932 Example Product 12400633526171902024_123176 Example Product 12400633526171902025_01420 Example Product 12400633526171902025_02-2336 Example Product 12400633526171902025_03-5092 Example Product 12400633526171902025_04-7848 Example Product 12400633526171902025_05-10604 Example Product 12400633526171902025_06-13360 Example Product 12400633526171902025_07-16116 Example Product 12400633526171902025_08-18872 Example Product 12400633526171902025_09-21628 Example Product 12400633526171902025_10-24384 Example Product 12400633526171902025_11-27140 Example Product 29803874428822023_1113603The logic restarts here when an new product is being evaluated.Example Product 29803874428822023_1212626 Example Product 29803874428822024_0111649 Example Product 29803874428822024_0210672 Example Product 29803874428822024_039695 Example Product 29803874428822024_048718 Example Product 29803874428822024_057741 Example Product 29803874428822024_066764 Example Product 29803874428822024_075787 Example Product 29803874428822024_084810 Example Product 29803874428822024_093833 Example Product 29803874428822024_102856 Example Product 29803874428822024_111879 Example Product 29803874428822024_12902 Example Product 29803874428822025_01-75 Example Product 29803874428822025_02-1052 Example Product 29803874428822025_03-2029 Example Product 29803874428822025_04-3006 Example Product 29803874428822025_05-3983 Example Product 29803874428822025_06-4960 Example Product 29803874428822025_07-5937 Example Product 29803874428822025_08-6914 Example Product 29803874428822025_09-7891 Example Product 29803874428822025_10-8868 Example Product 29803874428822025_11-9845 I would greatly appreciate any advice on the above. I tried using the Multirow and Multifield tools to achieve the solution I'm after but hitting a roadblock.

Advice for Solution: Rolling Update of Inventory based on conditions - MultiRow Multifield

Hi Community,

I'm stuck on a problem where I want to update the monthly Inventory for a number of products in the below dataset when the Inventory is below the Inventory Reorder Point in a given month. The Inventory value would be updated in the row that is X amount of months away (X = Lead Time (months) from the first instance the Inventory falls below the Inventory Reorder Point.

Below is the example dataset with notes that further explain the problem.

Product	Demand	Lead Time (months)	Inventory Reorder Point	Inventory Reorder Quantity	Year Month	Inventory	Notes
Example Product 1	2400	6	33526	17190	2023_11	39004
Example Product 1	2400	6	33526	17190	2023_12	36248
Example Product 1	2400	6	33526	17190	2024_01	33492	Inventory falls below Inventory Reorder Point
Example Product 1	2400	6	33526	17190	2024_02	30736
Example Product 1	2400	6	33526	17190	2024_03	27980
Example Product 1	2400	6	33526	17190	2024_04	25224
Example Product 1	2400	6	33526	17190	2024_05	22468
Example Product 1	2400	6	33526	17190	2024_06	19712
Example Product 1	2400	6	33526	17190	2024_07	16956	I want the Inventory to be updated here based on what the lead time is ( 6 Months in this instance). The inventory would be updated with the Reorder Quantity: Inventory + Reorder Quantity
Example Product 1	2400	6	33526	17190	2024_08	14200	The rows afterwards would be updated based on this formula: Previous Row Inventory - Demand.
Example Product 1	2400	6	33526	17190	2024_09	11444
Example Product 1	2400	6	33526	17190	2024_10	8688
Example Product 1	2400	6	33526	17190	2024_11	5932
Example Product 1	2400	6	33526	17190	2024_12	3176
Example Product 1	2400	6	33526	17190	2025_01	420
Example Product 1	2400	6	33526	17190	2025_02	-2336
Example Product 1	2400	6	33526	17190	2025_03	-5092
Example Product 1	2400	6	33526	17190	2025_04	-7848
Example Product 1	2400	6	33526	17190	2025_05	-10604
Example Product 1	2400	6	33526	17190	2025_06	-13360
Example Product 1	2400	6	33526	17190	2025_07	-16116
Example Product 1	2400	6	33526	17190	2025_08	-18872
Example Product 1	2400	6	33526	17190	2025_09	-21628
Example Product 1	2400	6	33526	17190	2025_10	-24384
Example Product 1	2400	6	33526	17190	2025_11	-27140
Example Product 2	980	3	8744	2882	2023_11	13603	The logic restarts here when an new product is being evaluated.
Example Product 2	980	3	8744	2882	2023_12	12626
Example Product 2	980	3	8744	2882	2024_01	11649
Example Product 2	980	3	8744	2882	2024_02	10672
Example Product 2	980	3	8744	2882	2024_03	9695
Example Product 2	980	3	8744	2882	2024_04	8718
Example Product 2	980	3	8744	2882	2024_05	7741
Example Product 2	980	3	8744	2882	2024_06	6764
Example Product 2	980	3	8744	2882	2024_07	5787
Example Product 2	980	3	8744	2882	2024_08	4810
Example Product 2	980	3	8744	2882	2024_09	3833
Example Product 2	980	3	8744	2882	2024_10	2856
Example Product 2	980	3	8744	2882	2024_11	1879
Example Product 2	980	3	8744	2882	2024_12	902
Example Product 2	980	3	8744	2882	2025_01	-75
Example Product 2	980	3	8744	2882	2025_02	-1052
Example Product 2	980	3	8744	2882	2025_03	-2029
Example Product 2	980	3	8744	2882	2025_04	-3006
Example Product 2	980	3	8744	2882	2025_05	-3983
Example Product 2	980	3	8744	2882	2025_06	-4960
Example Product 2	980	3	8744	2882	2025_07	-5937
Example Product 2	980	3	8744	2882	2025_08	-6914
Example Product 2	980	3	8744	2882	2025_09	-7891
Example Product 2	980	3	8744	2882	2025_10	-8868
Example Product 2	980	3	8744	2882	2025_11	-9845

I would greatly appreciate any advice on the above. I tried using the Multirow and Multifield tools to achieve the solution I'm after but hitting a roadblock.

Workflow

Transformation

Expression

Accepted answers

CoG

Here you are! A sample workflow that should do what you're looking for. Best wishes!

Main Workflow:

Image 1.png

Batch Macro:

Image 2.png

Iterative Macro:

Image 3.png

Sample Workflow.yxzp

All comments

CoG

There are some oddities in the data, that generate questions for me to better understand your use-case:

Where do your [Inventory] numbers come from because the formula you mention of subtracting the demand does not match for any of the rows, but what you are requesting would overwrite those values?
What happens (as is the case for Example Product 2) when the 3 month Lead time causes inventory to drop low enough that the reorder quantity is not enough to raise the inventory above the reorder point, does that month become the start of the next lead time calculation?
Does the lead time calculation need to be repeated until the end of the dates in the data set (i.e. there would be ~3 reorders for Example Product 1 before 2025_11)?

This looks to me like you would need a batch macro that feeds into an iterative macro.

Kaseykreid

Hi Andrew,

Thanks for your response! Here are the answers to your questions.

1. The Inventory numbers were generated already from a separate process. This dataset is an example of the extracted data I'm working to transform and essentially correct the Inventory values. For more context, this dataset is meant to be an Inventory forecast. What I'm requesting would overwrite the values and then continue overwriting the values after the first time the Inventory falls below the Inventory Reorder Point for each product.

2. Yes that's right. When the inventory drops below the Inventory Reorder Point again, that month will start the next lead time calculation.

3. Yes the lead time calculation would need to be repeated until the end of the dates in the data set.

CoG

Here you are! A sample workflow that should do what you're looking for. Best wishes!

Main Workflow:

Image 1.png

Batch Macro:

Image 2.png

Iterative Macro:

Image 3.png

Sample Workflow.yxzp

Kaseykreid

Much appreciated Andrew! This helped me a lot! Thank you for your quick response!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703