Running first out calc

[MT4T]

Hello

I have two tables with key Short_model. In Totals are aggregated totals out of various categories. In AVAILABILITY i have an Oldest>Newest data with daily, production qty, forecast of sales qty and sold.

I need to add qty from TOTALS to AVAILABILITY and do running total with Whole_qty and Sold_qty for ongoing stock for find a point in time when orders end, and stock is not booked by customer. Or find a point in time which Mps_qty are produced for delivery and which will catch dust on stock. Regarding this I should consider our forecast qty for production.

______________________SOURCES__________________________

AVAILABILITY:
LOAD * Inline [

Short_model, Delivery_date, MPS_qty, Forecast_qty, Sold_qty
55FN, 2022-11-01, 5, 0, 3
70BL, 2022-11-01, 0 , 7, 5
55FN, 2022-11-03, 0, 0, 1
40UN, 2022-11-05, 4, 0, 2
55FN, 2022-11-07, 7, 4, 2
40UN, 2022-11-09, 10, 0, 3
70BL, 2022-11-11, 2 , 0, 3
45UL, 2022-11-13, 2, 0, 2
52IN, 2022-11-14, 3, 1, 2
40UN, 2022-11-14, 2, 3, 7
45UL, 2022-11-15, 3, 4, 9
52IN, 2022-11-16, 2, 1, 2
];

TOTALS:
Load * Inline [
Short_model, FFS_qty, Dummy_qty, TBC_qty, Commited_qty, Whole_qty
55FN, 3, 5, 4, 2, 30
70BL, 2, 3, 1, 7, 32
40UN, 1, 1, 3, 4, 22
45UL, 4, 2, 5, 3, 27
52IN, 2, 3, 9, 4, 19
];

[vinieme12]

,can you post a sample expected output??

"do running total with Whole_qty and Sold_qty for ongoing stock for find a point in time when orders end, and stock is not booked by customer. Or find a point in time which Mps_qty are produced for delivery and which will catch dust on stock. Regarding this I should consider our forecast qty for production."

[MT4T]

Running stock should look like this. At start it has value from TOTALS and with every row based on Short_model it should substract Sold_qty which result is running stock. Calculated value of running stock should be pull into next appear same Short_model as Whole_qty.

LOAD * Inline [

Short_model, Delivery_date, MPS_qty, Forecast_qty, Sold_qty, Whole_qty, Running_stock
55FN, 2022-11-01, 5, 0, 3, 30, 27
70BL, 2022-11-01, 0 , 7, 5, 32, 27
55FN, 2022-11-03, 0, 0, 1, 27, 26
40UN, 2022-11-05, 4, 0, 2, 22, 20
55FN, 2022-11-07, 7, 4, 2, 26, 24
40UN, 2022-11-09, 10, 0, 3, 20, 17
70BL, 2022-11-11, 2 , 0, 3, 27, 24
45UL, 2022-11-13, 2, 0, 2, 27, 25
52IN, 2022-11-14, 3, 1, 2, 19, 17
40UN, 2022-11-14, 2, 3, 7, 17, 10
45UL, 2022-11-15, 3, 4, 9, 25, 14
52IN, 2022-11-16, 2, 1, 2, 17, 15
];

[MT4T]

I have uploaded an app with sample data.

Cols have different naming but overall, they are the same.

MATERIAL_MPS table is AVAILABILITY

POLIST table is TOTALS

Key is [PO] field. 

I need to peek value QTY_Commited for each PO into MATERIAL_MPS table. And in every row based on PO do sum considering last value from QTY_Commited minus Material_MPS_Qty for this PO.

[MT4T]

I found that in the table chart there is an option for accumulation modifier. And it calculates exactly what I want. But too slow i want to do same in script.

Below is expression from chart.

Sum({1<[$(=Replace(GetObjectField(0),']',']]'))]={"=Only({1}[$(=Replace(GetObjectField(0),']',']]'))])>='$(=Replace(Replace(MinString([$(=Replace(GetObjectField(0),']',']]'))]),'''',''''''),'$','$''&'''))' and Only({1}[$(=Replace(GetObjectField(0),']',']]'))])<='$(=Replace(Replace(MaxString([$(=Replace(GetObjectField(0),']',']]'))]),'''',''''''),'$','$''&'''))'"},[$(=Replace(GetObjectField(1),']',']]'))]={"=Only({1}[$(=Replace(GetObjectField(1),']',']]'))])>='$(=Replace(Replace(MinString([$(=Replace(GetObjectField(1),']',']]'))]),'''',''''''),'$','$''&'''))' and Only({1}[$(=Replace(GetObjectField(1),']',']]'))])<='$(=Replace(Replace(MaxString([$(=Replace(GetObjectField(1),']',']]'))]),'''',''''''),'$','$''&'''))'"}>}Aggr(RangeSum(Above(If(Count([$(=Replace(GetObjectField(0),']',']]'))]) * Count([$(=Replace(GetObjectField(1),']',']]'))]) > 0,  (　(BO_WH_Qty+MPS_Qty)-(Total_Order_Qty)　)  + Sum({1<[$(=Replace(GetObjectField(0),']',']]'))]={"=Only({1}[$(=Replace(GetObjectField(0),']',']]'))])>='$(=Replace(Replace(MinString([$(=Replace(GetObjectField(0),']',']]'))]),'''',''''''),'$','$''&'''))' and Only({1}[$(=Replace(GetObjectField(0),']',']]'))])<='$(=Replace(Replace(MaxString([$(=Replace(GetObjectField(0),']',']]'))]),'''',''''''),'$','$''&'''))'"},[$(=Replace(GetObjectField(1),']',']]'))]={"=Only({1}[$(=Replace(GetObjectField(1),']',']]'))])>='$(=Replace(Replace(MinString([$(=Replace(GetObjectField(1),']',']]'))]),'''',''''''),'$','$''&'''))' and Only({1}[$(=Replace(GetObjectField(1),']',']]'))])<='$(=Replace(Replace(MaxString([$(=Replace(GetObjectField(1),']',']]'))]),'''',''''''),'$','$''&'''))'"}>}0), 0), 0, 6)), ([$(=Replace(GetObjectField(1),']',']]'))], (Numeric, Ascending), (Text, Ascending)), ([$(=Replace(GetObjectField(0),']',']]'))], (Numeric, Ascending), (Text, Ascending))))