Desired output: Table on customer, delivery level showing receipt date (fixed), calculated shipdate
- 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?
do you mind providing a sample calculation with sample dates/numbers?
I have added a project with 3 tables, tc, t1 and t2.
tc you see the calendar with weekend flag (I haven’t added holidays for the example)
t1 holds each store with indication of receipt day in week (1 to 7 / Mon to Sun) as well as transportation days (excl weekends)
t2 holds the stores with possible receipts days per campaign
File ‘StoreShipCalendar’ is used as input from a previous project, and split into t1 and t2 in this project.
Shipdate based on receiptdate and calendar.morph (4.9 KB)
StoreShipCalendar.xlsx (337.4 KB)
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.
Shipdate based on receiptdate and calendar.morph (20.6 KB)
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.
Great - Thanks!
It looks a bit heavy, though seems to work… I’ll have a play around this with real data and get back if any further
Finally got time to play around with this one.
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.