Skip to main content
Solved

Trying to calculate On Time delivery

  • November 27, 2024
  • 0 replies
  • 343 views
Forum|alt.badge.img+4

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.

Best answer by MarkShnierYou

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.  

MarkShnierYou
Forum|alt.badge.img+24

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.  

Mark Shnier. Your Quickbase Coach
    Forum|alt.badge.img+4
    • ChrisSwirtz1
    • Author
    • Registered
    • November 27, 2024

    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?

    Forum|alt.badge.img+4
    • ChrisSwirtz1
    • Author
    • Registered
    • November 27, 2024

    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...

    Terms & ConditionsAccessibility statement