On time in full (OTIF)¶
How many orders arrived complete and on the promised date?
Current value (inventory case data): 74.5% - ✕ Act. Open the case.
| Field | Value |
|---|---|
| Definition | Orders whose every line was delivered no later than the promised date and with the full quantity, divided by all orders. |
| Formula | count(orders with all lines on time AND in full) / count(orders) |
| Unit | % |
| Granularity | Order; reported weekly. |
| Direction | Higher is better ↑ |
| Target and thresholds | ≥ 90.0% (amber ≥ 80.0%) |
| Parameters | – |
| Owner | Logistics Manager |
| Source | Order lines (demand from UCI Online Retail II) + synthetic stock simulation |
| Input | table order_lines, columns order_id, qty_ordered, qty_shipped, promised_date, delivered_date |
| Recommended chart | bar - Failure decomposition (late / short / both) as a sorted bar. |
| Related KPIs | Fill rate (units), Stockout rate (SKU-days) |
How to read it¶
The customer's view: one missing line fails the whole order. Always split the failures into "late" and "short" - they have different owners.
Common misreadings¶
- OTIF is much stricter than fill rate on multi-line orders; comparing the two numbers directly misleads.
- Using the requested date for some customers and the confirmed date for others changes the KPI silently.
Worked example (from the test suite)¶
| order_id | qty_ordered | qty_shipped | promised_date | delivered_date |
|---|---|---|---|---|
| 1 | 5 | 5 | 2024-01-10 | 2024-01-09 |
| 1 | 5 | 5 | 2024-01-10 | 2024-01-10 |
| 2 | 4 | 4 | 2024-01-10 | 2024-01-11 |
| 3 | 2 | 1 | 2024-01-10 | 2024-01-10 |
| 4 | 3 | 3 | 2024-01-10 | 2024-01-10 |
Expected result: 50.00%. Order 1: both lines full and on time -> OK. Order 2: one day late -> fail. Order 3: short (1 of 2) -> fail. Order 4: OK. 2 OK of 4 orders = 0.5. Fill rate on the same data is 18/19 = 94.7%: OTIF is stricter.
This example is asserted in tests/test_formulas.py against the Python function and in tests/test_sql.py against the SQL below.
Implementations¶
Python function (the single implementation used by the site):
@kpi("otif")
def otif(df: pd.DataFrame) -> float:
line_ok = (
(df["qty_shipped"] >= df["qty_ordered"])
& df["delivered_date"].notna()
& (df["delivered_date"] <= df["promised_date"])
)
order_ok = line_ok.groupby(df["order_id"]).all()
return safe_ratio(order_ok.sum(), len(order_ok))
Reference SQL (tested on SQLite):
SELECT CAST(SUM(ok) AS REAL) / COUNT(*) FROM (
SELECT order_id,
MIN(CASE WHEN qty_shipped >= qty_ordered AND delivered_date IS NOT NULL
AND delivered_date <= promised_date THEN 1 ELSE 0 END) AS ok
FROM order_lines GROUP BY order_id)