Backlog and Lead times
I need to calculate a backlog for per product per day.
I have a module with Factory open days and closed days and a module with lead times by product.
Volume gets sent to production (factory) and then I need to take a lead time calc for the products.
The trick is that to work out how many future days in a row the factory is open (these can change depending if the factory open boolean is ticked) to see when the product will get produced and by equal to or more than the lead times.
E.g: the factory could be open Monday Thursday Friday. If the volume came in on Monday (with a 3 day lead time) then the backlog would get recognised on the Friday. The lead times and the factory open days can differ. Also the lead times can cross over days, weeks, months and years.
I have booleans for open days per factory and a count for the days open.
I also have lead times by month by product.
Hope this enough information?
the core solution is to use movingsum(value,1,lead time), which will give you your open days from that date.0
I need to get the amount of days open in the factory from the date. I then need to force the value into the day that is on the (in this case 3 days) counting all the days that the factory is open.
in that case it will be movingsum(value, -1,-lead time)
or maybe 0 instead of -1 depends if you want to include the current day from the count0
It is a bit more complicated than moving sum due to the calculation being in the future days and time span can be variable, depending on when the factory is open and closed. (Mon could be closed then Tue open then wed closed, Thu open, Fri open) this would make a 5 day lead time not 3. The closure could be all week, then closed for 2 days etc etc.