Calculating OTD report, 4 different dates

[J33]

Hi, we have an OTD-report (through our ERP) with 4 different dates. I simplified the names in the code.

The issue I have now:
When CONFIRMED does not have a date, but OLDCONF does -> It will not calculate the OLDCONF - SHIPMENT. How can I fix this?
And yes, this issue appears when the dates are not filled out according to the process (always first CONFIRMED), but mistakes happen.

REQDATE = Requested shipping date
CONFIRMED = Confirmed shipping date
OLDCONF = Old confirmed shipping date (if the supplier changes the date, we move the confirmed to old confirmed and renew the confirmed date)
SHIPMENT = Date when we received the shipment

if ({OLDCONF} <> date(0000,0,0) and {OLDCONF}>{CONFIRMED}) then
{CONFIRMED}-{SHIPMENT}
else if ({OLDCONF} = date(0000,0,0) and {CONFIRMED} = date(0000,0,0)) then
{REQDATE}-{SHIPMENT}
else if ({OLDCONF} = date(0000,0,0)) then
{CONFIRMED}-{SHIPMENT}
else
{OLDCONF}-{SHIPMENT}

EDIT: With this I try to get the difference in days, between REQDATE or CONFIRMED or OLDCONF date and SHIPMENT date.

 

[lbass]

You might first try simply changing your Report Options to "convert database null values to default" and see if that clears up the problem. Otherwise, do NOT check this and instead change your formula to check for nulls for any field that can be null. You must always do the null check the first time a field is referenced, so try rewriting the formula like this:

if (
(
isnull({OLDCONF}) or
{OLDCONF} = date(0000,0,0)
) and
(
isnull({CONFIRMED}) or
{CONFIRMED} = date(0000,0,0)
)
) then
{REQDATE}-{SHIPMENT} else
if (
(
not isnull({OLDCONF}) and
not isnull({CONFIRMED}) AND
{OLDCONF} <> date(0000,0,0) and
{OLDCONF}>{CONFIRMED}
) or
(
ISNULL({OLDCONF}) OR
{OLDCONF} = date(0000,0,0)
)
) then
{CONFIRMED}-{SHIPMENT}
else
{OLDCONF}-{SHIPMENT}

-LB

 

[J33]

Thanks for your answer, I changed in the report as you suggested - However it still does not calculate the difference as it should.

As an example:
REQDATE 2018-10-31, OLDCONF 2018-10-31, CONFIRMED null/blank, SHIPMENT 2018-12-04
This gives the difference "0 days", and it should be "-34 days".

I still can't figure this out.. Please help if you have more ideas.

 

[lbass]

Did you copy my formula or did you rewrite your formula? I wonder if you recreated it correctly, as i believe it should work for the example you gave. Try copying the formula from my post.

-LB

 

[J33]

I copied the formula from your post to notepad:
-> replaced the {CONFIRMED} etc. to the correct table names
-> copied the formula from notepad
-> Edit formula in Crystal
-> Erased the old formula and pasted your formula

The Difference value is in "Number" format and the dates are "Date" format, if it matters?

I also noticed that when I have:
REQDATE 2019-01-14, OLDCONF 2019-01-14, CONFIRMED 2019-01-21, SHIPMENT 2019-01-14
This also gives the difference "0", when it should be the difference between CONFIRMED and SHIPMENT.

 

[lbass]

In this last case, your are confirmed is not > oldconf, and therefore the formula reverts to oldconf-shipment which is 0. Also just realized that your first example is a case where confirmed is null, but oldconf is not, so you need to add one more condition to your formula. I’m not sure what you want to happen in that case. What you would subtract shipment from.

-LB

 

[J33]

When confirmed is null, but oldconf is not: Then I want to calculate the difference OLDCONF-SHIPMENT.

On Time Delivery report, where we want the difference calculated. This is the idea:

I haven't created the original formula, and struggling to get this right.. but I'm learning more every day. The OLDCONF > CONFIRMED is a check that the newer date has been entered in the correct box (CONFIRMED) and the old value in OLDCONF. Maybe there is an easier way to check that?

 

[lbass]

Shouldn't {OLDCONF} be an earlier date than {CONFIRMED}? If so, the operator is incorrect and instead should be:

{OLDCONF}<{CONFIRMED}

Or perhaps <= if you want to accept the same dates in each.

Just to make sure there aren't other odd results, I also explicitly added the case of null confirmed and nonnull oldconf. New formula:

if (
(
isnull({OLDCONF}) or
{OLDCONF} = date(0000,0,0)
) and
(
isnull({CONFIRMED}) or
{CONFIRMED} = date(0000,0,0)
)
) then
{REQDATE}-{SHIPMENT} else
if (
(
not isnull({OLDCONF}) and
not isnull({CONFIRMED}) AND
{OLDCONF} <> date(0000,0,0) and
{OLDCONF}<{CONFIRMED} //or <= possibly
) or
(
ISNULL({OLDCONF}) OR
{OLDCONF} = date(0000,0,0)
)
) then
{CONFIRMED}-{SHIPMENT} else
if
(
isnull({CONFIRMED})or
{CONFIRMED} = date(0000,0,0)
) and
not isnull({OLDCONF}) then
{OLDCONF}-{SHIPMENT} else
{OLDCONF}-{SHIPMENT}

If you are still getting incorrect results, please provide sample results in the chart formula you showed in your last post so I can troubleshoot this.

-LB

 

[J33]

LB, this works perfectly. Thank you very, very much for your help! I learned something new again