Dealing with Null Dates (Minimum Function issue)

[geestrong]

Hello,

I am using CR XI on Oracle ODBC

I am having issue with null dates. Here is the situation. I have to choose between two dates depending on this citeria:
Delivery Date and Start Date.

One of these dates will be the IN DATE. The problem is using the the minimum function it returns the NULL value.

Here is my logic:
This logic is in the detail section:
If (isnull({MAJOR_PROJECT_VIEW.DELIVERY_DATE}) or({MAJOR_PROJECT_VIEW.DELIVERY_DATE} = DateTime(0,0,0,0,0,0)))
then
minimum ({MAJOR_PROJECT_VIEW.START_DATE},{MAJOR_PROJECT_VIEW.ACT_CODE})
else
minimum ({MAJOR_PROJECT_VIEW.DELIVERY_DATE},{MAJOR_PROJECT_VIEW.ACT_CODE})

The formula should evalute all the Delivery Dates within the group and choose the earliest date that is not Null.

Is there a function that will not evaluated NULLs and return the correct results.

Thanks,
Gee

 

[lbass]

You could first convert your nulls to a future date like the following {@delivdatetime}:

if isnull({MAJOR_PROJECT_VIEW.DELIVERY_DATE}) or{MAJOR_PROJECT_VIEW.DELIVERY_DATE} = DateTime(0,0,0,0,0,0) then
datetime(9999,09,09,0,0,0) else
{MAJOR_PROJECT_VIEW.DELIVERY_DATE}

Then change your formula to:

If {@delivdatetime} = date(9999,09,09,0,0,0)then
minimum ({MAJOR_PROJECT_VIEW.START_DATE},{MAJOR_PROJECT_VIEW.ACT_CODE}) else
minimum ({@delivdatetime},{MAJOR_PROJECT_VIEW.ACT_CODE})

-LB

 

[geestrong]

LB

It appears to work in most of the criteria... I will modify the formula on some of the other criteria.

Thanks again LB

 

[geestrong]

LB,

I do not need to insert dates. I need to check that there is a date and it is not null and return that smallest dated if it exist. If there is no date then I do not need to display a date. Also, I am doing calculations, so I my number of days will come out very high.

Thanks,
Greg

 

[lbass]

You should use conditional suppression to suppress the formula:

{@delivdatetime} = date(9999,09,09)

What is the calculation you are doing? Please provide the contents of the formula.

-LB

 

[geestrong]

LB

Thanks for your reponse, I have it working... I created a formula called @checkNullDate. it returns True or False, then sort the by this by descending order that allows the the FALSE to be on top. Then a place the date field on the of the group levels.

Here is the formula:
If not isnull({MAJOR_PROJECT_VIEW.DELIVERY_DATE})
then
Maximum ({MAJOR_PROJECT_VIEW.COMPLETE_DATE},{MAJOR_PROJECT_VIEW.ACT_CODE})- Minimum ({MAJOR_PROJECT_VIEW.DELIVERY_DATE},{MAJOR_PROJECT_VIEW.ACT_CODE})
else
Maximum ({MAJOR_PROJECT_VIEW.COMPLETE_DATE},{MAJOR_PROJECT_VIEW.ACT_CODE})- Minimum ({MAJOR_PROJECT_VIEW.START_DATE},{MAJOR_PROJECT_VIEW.ACT_CODE})

This formula is working just fine.

Thanks,
Greg