Best Way to Find the Minimum Date following an if statement 1

[willz99ta]

Hi,

I am trying to find a way to show the minimum date for all the records where another variable = "Product Summary".

Any idea about the best way to do that?

Thanks for your help,
Will

 

[IanWaterman]

Add another var formula

@date
whileprintingrecords;
global stringvar yourvar;
global datevar newdate

If yourvar = "Product Summary" and newdate > {yourdatefield}
then newdate:= {yourdatefield}

You will need a reset formula at an appropriate location

@reset

whileprintingrecords;
global datevar newdate:=date(2100,01,01)

This is a date way in future so that first record encountered will always be less.

Ian

 

[lbass]

Or you could write a formula like this:

if {table.field} = "Product Summary" then {table.date}

Place this in the detail section and insert a minimum on it at the group or report level.

-LB

 

[willz99ta]

LB,

That's exactly what I originally tried, but my minimum field in the Report Footer is always blank.

Thanks for your help,
Will

 

[willz99ta]

The problem seems to be that it picks up NULL values and displays them as the minimum date.

Also, the date field I am pulling from is a string variable type.

Whatcha think,
Will

 

[lbass]

You have to decide what you want to happen with the records where the field = "Product Summary", but the date is null. Maybe you want to set the date to today's date or maybe you want to ignore them--only you can tell us.

If you want to ignore them, you can set up a formula for the date where nulls are set to some future date, as in the following:

//{@date}:
if isnull({table.date}) or
trim({table.date}) = "" then
"2999/09/09" else //set this up in the same format as your field displays
{table.date}

Then use this formula:

if {table.field} = "Product Summary" then {@date}

Then you can insert a minimum on this.

-LB

 

[willz99ta]

Crud -- I am still getting a blank return from the minimum of:

if {table.field} = "Product Summary" then {@date}

 

[lbass]

I don't see how that is possible. You should check how {@date} appears in the detail section--assuming you set it up as I showed earlier.

-LB

 

[willz99ta]

I got it. I changed the code to this and it worked:

if {table.field} = "Product Summary" then {@date}
else "09/09/2999"

The problem was that when the table.field was not Product Summary it was seeing a null value for some reason.

Thanks for your help again,
Will