Supplier Delivery Formula Using +/- 3 Days

[bstafford21]

I have a Delivery Performance report I use for our Supplier.

I am in need of modifying this delivery % to allow a variation of =/- 3 days.

Our supplier ships from overseas and usually the delivery is help up by customs so have to accomadate for this interruption.

Can someone tell me how to add a variable into the formula?

(Sum ({@Late Delivery}) / Count({@Late Delivery})) * 100

This formula above is how I track currently
This next formula is just for

If {GRN.Due Date} >= {GRN.Date Received}Then 1 Else 0


Thanks for any help

Bill

 

[IanWaterman]

The easiest wy will be to modify your late delivery formula. Giving it a 3 day lee way.

If {GRN.Due Date} >= dateadd("d",3,{GRN.Date Received})Then 1 Else 0


Ian

 

[bstafford21]

Hi Ian,

Thanks for the reply on the formula suggestion.

I have put this new in and had a go and now
my Del % dropped to 1.7%

Would this need to be subtracted from 100 maybe?

I am trying to understand the formula, but can't
figure out what it is doing.

Thanks

Bill

 

[lbass]

Is the sign in your late formula incorrect? Shouldn't the delivery be considered late if it was received AFTER the due date + 3 days?

-LB

 

[bstafford21]

Yes the delivery would be considered late after 3 days, I agree, so my formula is wrong?

The delivery for example if Due today 28/11/2008

would allow the supplier +/- 3 days for delivery,

they can be consider 100% between 25/11/2008 and 01/12/2008

So trying to get a formula that will not count it late if

between that time period.

Bill

 

[lbass]

So you want to account for business days only?

-LB

 

[bstafford21]

I think business days are fair, otherwise you have to manually deduct them from the calculation. Is there a way?

Bill

 

[lbass]

Try Ken Hamady's formula for adding business days:

http://www.kenhamady.com/form17.shtml.

Your formula as currently written is backwards--should be like this:

If {GRN.Date Received} > {GRN.Due Date} + 3 Then
1 Else 0

-LB

 

[bstafford21]

Checking this with a new suggestion, I still can't get a real answer

1.Original Formula
If {GRN.Due Date} >= {GRN.Date Received}Then 1 Else 0

2.Adding 3 Days Tolerance/Grace Period
If {GRN.Due Date} >= dateadd("d",3,{GRN.Date Received})Then 1 Else 0

3.Other version
If {GRN.Date Received} > {GRN.Due Date} + 3 Then 1 Else 0

Not getting the answer I am expecting when I only have 3 out of 100 late.

should be 97% using a +/- 3 Day Grace
but getting 65% with the 1st formula

but getting 1.7% with 2nd formula

but getting 30.8% with 3rd formula

Now I am really confused lbass

 

[lbass]

You called your formula "Late Delivery" so I assumed you were trying to create a percentage late calculation. So which is it? Late or OnTime? Also, are deliveries that have not been received yet potentially in the calculation? Did you decide to ignore business days?

-LB

 

[bstafford21]

Hi,

Sorry if there is confusion with the name. This report is named Delivery Performance.

I was just talking in terms of Late Deliveries. The purpose of the formula is to show what % of deliveries are on time.

At the moment I am just ignoring the business days, may play with that later.

So hope that clears it for the purpose.

Bill

 

[lbass]

You didn't respond to this: Also, are deliveries that have not been received yet potentially in the calculation? In other words, can the date delivered field sometimes be null, even though there is a due date?

-LB

 

[bstafford21]

Sorry, I did forget to answer that one.

My report only looks at what has been received up to that
date, no future Purchase Order dates are looked at, only what has been booked into the system and given a GRN number as received and then the due date is pulled in and compared.

Bill

 

[lbass]

Use a formula like this {@Ontime}:

if {GRN.Date Received} > {GRN.Due Date} + 3 Then 0 Else 1

Then use a formula like:

sum({@OnTime})% count({GRN.Due Date}) //count any recurring field

If this is incorrect, you need to make sure at the detail level that you don't have row inflation (repeating records per delivery).

-LB

 

[bstafford21]

I have done the checking of repeats, there is no problem there as the only data is from GRN data file.

So using Ontime formula for the 1st stage?

if {GRN.Date Received} > {GRN.Due Date} + 3 Then 0 Else 1


When do I or where in the report to use the 2nd stage?

Bill

 

[bstafford21]

HI LB,

I have this all set now with what you suggested and I changed the name to Ontime,

I agree, this is better description and also the formula works the way we need it to now.

I will go review the other area for Business Days now.

Thanks for straightening me out on this, appreciate the help.

Have a good one,

Bill