sent dates, delivery dates, slippage rate ... aaaaaaargh!

[kev100]

Ok, I've been banging my head against this all day. Scenario: Publishing company has a production schedule with:

(a) an intended file despatch to printer date field

(b) an actual date files are sent to printer field

© an expected book delivery date field, and

(d) an actual date books are received field.

 

I need to generate a report to see see how often, and by how much the printer slips in its 10 week turnaround commitment.

 

First, I set field X to do a minus b, to see how late we were sending the files

Next I set field Y to do c minus d to see how late the printer is delivering books

 

However, until the books are actually delivered I can't track if the books are running late, and if so, by how much.

 

So, I've another field (Z) for b (files sent) + 10 weeks (70 days) and this gets updated manually on a weekly basis so we can see any slippage.

 

What I need to do is calculate X and Y for my report, but I can only calculate Y is the books have been delivered.

 

So what I want to do is calculate Y where possible, but if not, use the value from Z.

 

I've tried various Case functions, If functions and Is(Empty) functions, but I can't get FM to include the value from another field in a calculation, if the original calculation field is empty.

 

Sorry this is so long-winded.

 

Cheers,

 

Kev.

[Techphan]

If (IsEmpty(Y);Z;Y) should do it.

[kev100]

Hi, yes, I got it in the end, and was just about to post the outcome:

 

If ( IsEmpty ( Finished books Actual ); Finished books date - despatch act plus 10 weeks ; Finished books date - Finished books Actual)

 

Thanks.

 

kev.