Trying to create a column with a variable total of another column based on the number of days listed in a second column. Value would be the rolling total of demand for a product_location for the amount of days it takes to get a new delivery from each day(planned delivery time). So I need a formula to calculate the demand for the last X amount of days, this day value listed in a column on the report.
Any help would be appreciated.
Product APO Location Product_Location X-Plant Material Status MRP Type Date New/Lost Business New/Lost Business Override ROP Net Adjusted History v_AUX-122 Horizon Days Max Planned Delivery Time
2D73HT80 D0CG 2D73HT80_D0CG VB 8/28/2018 640 0 0 2
2D73HT80 D0CG 2D73HT80_D0CG VB 8/29/2018 640 0 0 2
2D73HT80 D0CG 2D73HT80_D0CG VB 8/30/2018 640 0 0 2
2D73HT80 D0CG 2D73HT80_D0CG VB 8/31/2018 640 0 0 2
2D73HT80 D0CG 2D73HT80_D0CG VB 9/3/2018 640 0 0 2
2D73HT80 D0CG 2D73HT80_D0CG VB 9/4/2018 640 0 0 2
2D73HT80 D0CG 2D73HT80_D0CG VB 9/5/2018 640 0 40 2
2D73HT80 D0CG 2D73HT80_D0CG VB 9/6/2018 640 0 0 2
2D73HT80 D0CG 2D73HT80_D0CG VB 9/7/2018 640 0 320 2 APO ROP SWEEPS CHECK 8.27.3.xls (141.0 KB)
I’m confused and not 100% clear on your requirements
It sounds like you want to know the current “demand” of a product at a point in time. This will include only any products in “demand” whose delivery date has not been reached, yes?
If so what column measures or identifies this demand bearing in mind the terminology you use may be very unique to your sector of work
Thank you for your time.
The demand is measured in the ROP Net adjusted History column. The lead time for our delivery to a specific location is the Planned Delivery Time column. What I need for each row is the demand for that row/date, plus the demand for the last X amount of days. Where X is the amount of days it takes to get a delivery or the Planned Delivery Time. In the sheet I attached there is just one material listed and one location. The Planned delivery time for this material location combination is 2 days. So in this example every row would have the demand for that date plus the demand for the day before, 2 days. I of course have many more items and locations so the Planned Delivery time is going to differ for each.
… you are right, the formula does not work with variable offset
The “offset”-parameter actually is the 3rd element in the syntax:
previous(object; reset dimension; offset)
if 2nd element is a number it will be recognized as the “offset”
if 2nd element is a dimension or variable it will be recognized as the “reset dimension”
I did a small test with 3 elements, but “offset” can only be set to a fixed number. When using a variable, the formula ignores this and set the offset always to “1”
(BOB member since 2018-12-29)