I have a big challenge again. I would like to explain it to you and I look forward to everyone’s help in finding the best solution.
Well, I have a volume of packages that must be sent daily, but my stores have a receiving capacity. I would like this to be subtracted and the difference added to the volume I have to send the next day.
Example: For 2023-10-12 I have a sending volume of 9000 and a receiving capacity of 8000, so there will be 1000 left. And for 2023-10-13 I have 8000 + 1000 (remaining from the previous day ) and so on for the rest of the week.
You can just use the Lag column to duplicate your quantity column and drop it down by 1 row.
In your example: That way the 9000 and 8000 are on the same row. Then you would subtract the lag qty column from the original to get your difference. (9000-8000=1000)
After that it gets kind of vague what the result column should look like. Not sure if you want to actually add that back to the original column afterwards, but you can use the Math or Column Expressions nodes to do these expressions easily.
This assumes that there are no special conditions when this calculation should not occur, such as more than 1 day passing between rows or the changing between a defined week.
I sent you a print, following this example. On 2023-10-09 I can send 100% of my demand, and I have 0 BACKLOG, on 2023-10-10 I have 1163 BACKLOG so on 2023-10-11 I have the demand of 12092 + 1163 Totaling 13255 - 8000 (my receiving capacity) I have a backlog of 5255.
Basically he needs to look at the previous day and check if he has a backlog, if so, he needs to add it to the demand and subtract it from the capacity and so on for the rest of the days.
I have something started over here, but I can’t tell how you are calculating BACKLOG… Look at column F on the Date 2023-10-12. How did you arrive at 7,143? It seems like there may be some circular reference logic going on?
The backlog column is the forecast value - capacity. And the new forecast column has to be the previous day’s backlog value + the current forecast value.
Be sure to do some testing and review . It was difficult for me to internalize your calculation descriptions as they referenced columns that I would need to calculate and create. I believe that this is what you were describing though.
There are some strange numbers, I’m trying to understand the logic you used to apply them, but I’m counting on your help to finalize these adjustments. Thank you very much, I don’t know how to thank you, this will help me a lot.
It was clearer with the additional Data. I grouped it by the Origin / Destin / Week / Year. (Purely based on the assumption that differences in Origin would represent a different event as well)
Take a look. It required a loop approach now that there was increased variability in Grouping, starting days, reset points, etc.
Yes, it must be calculated according to destiny. I think it’s just an adjustment to some logic, probably in the expression column, but I’m not able to come up with a rationale. I sent the complete table above, can you help me again?
An important point, if the BACKLOG is <0 => 0, the Forecast Value column will either show the same value as the Shipping volume column or it will add up to the Backlog generated the previous day.
