×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Calculating OTD report, 4 different dates

Calculating OTD report, 4 different dates

Calculating OTD report, 4 different dates

(OP)
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.

RE: Calculating OTD report, 4 different dates

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

RE: Calculating OTD report, 4 different dates

(OP)
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.

RE: Calculating OTD report, 4 different dates

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

RE: Calculating OTD report, 4 different dates

(OP)
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.

RE: Calculating OTD report, 4 different dates

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

RE: Calculating OTD report, 4 different dates

(OP)
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?

RE: Calculating OTD report, 4 different dates

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

RE: Calculating OTD report, 4 different dates

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close