In excel i can use a IF function to handle it, so I tried “lag column” node to compare values in two row and get the smaller one. But soon I realize it did not work since this comparision is dynamic in excel across whole sheet, and “lag column” is a static one.
So I guess I need a loop function, step from the largest date(and also the “should be” largest data), comparing 2 row at a time. Then move it all the way down to finish the task.
Do you mind explaining the logic in a bit more detail? Been trying to work out why for 2023-07-05 to 2023-07-09 the desired result is 10 in all cases…
From what I read you are already thinking into the right direction - another alternative could be column expressions node with multi-row access enabled:
About “2023-07-05 to 2023-07-09 the desired result is 10 in all cases”
This is a supply chain case that try to optimize the supply, and data here is a cumlative supply of a material. So decreasing number means “someone take the material away” which did not make sense.
So my purpose is to make this series monotonically increasing, by decreasing those days has lower value than future day supply.
In this case, the “bottleneck” will be on 2023-07-09, which is lower than the previous Jul 6 - Jul 8 value, so the logic is:
Starting from the last day, compare value(today) with value(yesterday)
if value(today)< value(yesterday)
value(yesterday) = value(today)
and all the way down.
I see that makes it clearer. And let’s say when you change yesterday to todays value and then move “up” one row, (so in previous iteration yesterday is today in the next iteration) so the consider the “new” value already
may need to learn on loop well on it’s dynamics.