Hello,
I am currently trying to develop a WF that calculates actual lead times of orders (time it takes for an order to be placed, shipped and arrive to its needed location). Then I need to calculate metrics based on the actual lead time vs the estimated time the suppliers said the order would take.
Orders can be one or many rows. Each week, the quantities of orders are updated, if there is a change, it indicates some of the order has shipped.
Example data:
| Week | Order | QTY | Scheduled_Ship_Date |
| 1 | A | 10 | Date1 |
| 1 | A | 10 | Date2 |
| 1 | B | 5 | Date1 |
| 2 | A | 5 | Date1 |
| 2 | A | 10 | Date2 |
| 2 | B | 5 | Date1 |
This indicates that 5 have shipped from Order A between week 1 and 2.
I have been able to calculate the amounts that ship week by week. What I need to do next is calculate metrics. One of the metrics involves calculating the number of days between the initial scheduled shipment date and when the order shipped. The issue is that the scheduled ship date can change from one week to a next. Additionally, order lines can be broken apart from one week to the next, so an order that is initially 1 row of QTY 10 can be 2 rows of QTY 5 and 5 the next week.
What I've done is taken an initial "snapshot" of all new orders with their original shipment dates. I'd like to compare and correctly append that snapshot to the orders that have shipped.
Ex:
Snapshot
| Week | Order | QTY | Scheduled_Ship_Date |
| 1 | A | 10 | Date1 |
| 1 | B | 50 | Date2 |
Shipment record
| Week | Order | QTY | QTY_SHIPPED | Scheduled_Ship_Date |
| 1 | A | 5 | 5 | Date1 |
| 1 | B | 50 | 0 | Date2 |
| 2 | A | 3 | 0 | Date1 |
| 2 | A | 2 | 0 | Date3 |
| 2 | B | 45 | 5 | Date4 |
Order A was split into two lines between weeks 1 and 2, and a new shipment date for one line was changed.
What id like to create is something like the following:
| Week | Order | QTY | QTY_SHIPPED | Scheduled_Ship_Date | Initial_Schdule_Ship_Date |
| 1 | A | 5 | 5 | Date1 | Date1 |
| 1 | B | 50 | 0 | Date2 | Date2 |
| 2 | A | 3 | 0 | Date1 | Date1 |
| 2 | A | 2 | 0 | Date3 | Date1 |
| 2 | B | 45 | 5 | Date4 | Date2 |
How could I go about doing this? These tables are simplified versions of the data that I'm using but I can provide more clarification if needed. Thank you.
