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" if not.I now want to show an over all Percentage of how often we are On Time. Any suggestions on how to set up a report that would portray this information? I was looking for a formula that would count how many records where the On Time field = Yes so that I could maybe summarize that. Any thought on how to set this up would be greatly appreciated.

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.

Trying to calculate On Time delivery

MarkShnier__You
Qrew Legend
13 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
13 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
  13 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?
ChrisSwirtz1
Qrew Trainee
13 days ago
No, no relationships as of yet. I did think about that but I am uploading data from a spreadsheet to fill these tables and I have always had a hard time linking parent and child records together after they have been created in any kind of mass update. But I know with relationships and being able to utilize summary fields would probably help.
So far there are 6,108 shipment records. The company I am working for uses a platform called Odoo and it is terrible for doing reports and getting data out of it. As it is I have to export the data I am using and do several mods to it before I can even upload it into QBase. That being said I suppose I could just make getting the Max ship date for any Order #'s that have multiple shipments against them part of my prep routine for the spreadsheet before I upload it...
- MarkShnier__You
  Qrew Legend
  13 days ago
  Well, the ideal method to do this would be to upload a table of unique reference numbers into a parent table. If you're doing data manipulation and you're familiar with pivot tables maybe that's not too terrible for you. Then obviously the line item data has the reference number so you could make a relationship. In the relationship you would have a summary for whether or not that reference number had any failures, then you could make a formula on the reference number table similar to what you have on the line item table to determine if a whole reference shipped clean on time or not.
  
  Another technique for quickly creating unique reference numbers on a parent table is to make a summary report of orphaned line items where the reference number parent does not exist. Typically I do that by making a formula checkbox field, which is true on the parent reference number table and looking it up down to the children. If that checkbox is blank, then the record is an orphan. So you make a summary report by reference number of the orphans and manually click the button to copy these to another table. As manual processes go once you have this set up it literally takes five seconds to create the missing parent reference numbers.

Forum Discussion

Trying to calculate On Time delivery