Desired output: Table on customer, delivery level showing receipt date (fixed), calculated shipdate
Tables expected:
tc - Calendar including weekends and holidays
t1 - List of deliveries per store with receipt dates
t2 - Master data per store showing leadtime in transportation days (days from ship>receipt excl weekends and holidays - example ship Thursday, receipt following Tuesday = 3 [f,m,t] and not 5 [f,s,s,m,t])
Output with desired output
I have all three tables, and have merged t1 with t2 to get leadtime in the t1 list, but how do I calculate a shipdate based on input from tc?
Here is a proposed solution. It took me good couple hours to figure it out but it seems to work.
The idea is that for each calendar date we calculate possible shipment dates depending on leadtime. Since leadtime only accounts workdays, we calculate workdays for all dates 16 days back from each date. So it’s basically 16 sliding windows (1 day long, 2 days long, 3 days, etc.) that only counts workdays. 16 because the biggest leadtime is 12 workdays, so I added two more weekends (4 days) to get 16 calendar days.
Once we obtained possible shipdates for each leadtime for each delivery date, the matrix table is unpivoted and merged onto the shipments table.
To add holidays, change [Weekend flag] to TRUE for the holidays.
I’ve also increased the main calendar by 2 weeks both ways to accommodate for larger leadtimes.
Another possible way to solve this problem (for any arbitrary leadtime, not just 1 to 12) is to calculate the number of work days for each delivery using iterations. But since the number of deliveries in the example is more than 10 thousand, running even a short iteration for each delivery can make full calculation rather long. With pre-calculated shipment dates it works instantly.
I added dimensions like warehouse/site and site specific holidays, and though it generates 50.000+ rows, it takes less than 20 seconds to calculate… Very impressive
The calculation of the possible shipdates is a bit heavy to wrap the head around, but the method works - Is it possible to have a function that does this based on user input on how many possible combinations are required (16 in your solution)?
Theoretically, it is possible. It would require passing a table between iterations, which can be done through exporting/importing a file between iterations. Basically, each iteration loads a calendar table from a file, shifts down the last column, then saves it back to the same file.