Forum Discussion

Qrew Trainee

29 days ago

Trying to calculate On Time delivery

I have a table that holds shipment data, Ship to, Reference #, Due Date, Ship Date, and then a formula field that returns "Yes" if the the the Ship Date is less than or equal to the Due Date and "No"...

reports

MarkShnier__You
29 days ago
One way to do this is to make a formula which calculates to 1 if the shipment was on time and 0 if it was not on time. Set the field type to be percent.

Then you can run a summary report to summarize your orders by say week or months and do an average of that field.

ChrisSwirtz1

Qrew Trainee

29 days ago

Thanks Mark, this worked great!

To dive into this further, the data I pulled was on a line by line basis where as a shipment may have multiple lines and each line could ship separately against the same due date. So there are instances where some lines on a shipment shipped on time but one not have made it out on time, which technically makes the overall shipment not on-time. When I was doing it on a spread sheet I would use a pivot table to group the shipment #'s and calculate on Ship Date looking for the Max Date. Can I do something similar in a summary report to get the On-time percentage at the overall Reference # level?

MarkShnier__You
Qrew Legend
29 days ago
... can you tell me if you have any kind of a relationship? Like One Reference # has many Line Items? Also how many records in your line items table?