Conditional Minimum

[peechy23]

I currently have a report that is grouped by Property.Id, Building.ID,Leased.ID and I want to pull the minimum newdate by leased.id where field4 equals "Yes"

i Tried minimum({Option.newdate},{Leased.ID}) which gives me the correct minimum date for the Leased.ID but I need to take it one step further and have it look for the minimum by where field4 equals "yes" So the data would be as below

Leased.ID NewDate Field4
12345 10/01/2011 Yes
12345 09/01/2011 maybe
12345 08/01/2011 no
12345 12/01/2011 yes

I would like the data returned to b e 10/01/2011

Any suggestions would be greatly appreciated and if this is not the place to post these types of questions please let me know as I am new to this forum.

 

[lbass]

Create a formula like this:

//{@yesformula}:
if {table.field4} = "Yes" then
{table.newdate}

Then you can insert a minimum on this formula at the lease ID group level. If you only want records that have this date to display, then go to report->selection formula->GROUP and enter:

{table.newdate} = minimum({@yesformula},{table.leaseID})

-LB

 

[peechy23]

Where do you put the //@yesforumla formula?

 

[fisheromacse]

a SQL expression would be the easiest way to do this.
Place the formula in the leaseID group header/footer.
If you put it in the details, it will show the same minimum value for all records in a group. I would guess that you will need to add a little more logic to the below to get it to where you need it.

//{%MinPerLeaseYes}
(
select min("newdate")
from Option
where "Option"."Field4" = 'Yes'
)

 

[fisheromacse]

doh! beat to the punch by lbass, and with an easier way too!

the yesformula would go into the details

 

[lbass]

Create the formula in the field explorer->formula-new, and then place it in the details and right click on it->insert summary->minimum if you want to see the result. If you just want to show those records that meet the criterion, you don't have to place it on the report at all--just follow the group selection steps.

fisheromacse,
A SQL expression is actually a better way to go depending upon the purpose of the report, but it is an advanced technique, that is often harder to grasp. To get a group minimum, you would have to set up the SQL expression like this:

//{%MinPerLeaseYes}
(
select min("newdate")
from Option A
where A."Field4" = 'Yes' and
A."Lease_ID" = Option."Lease_ID"
)

Then if the user wanted to limit records to the minimum date per group, the following could be added to the record selection formula:

{Option.newdate} = {%MinPerLeaseYes}

-LB

 

[peechy23]

I tried the first suggestion and I saw in the details that it populated the correct dates for the field. i want to show the minimum in the Leased.ID group. When I put the minimum of the yesformula they all show up blank. Is there an additional step I missed?

 

[lbass]

Sorry, change the formula to this:

if {table.field4} = "Yes" then
{table.newdate} else
date(9999,9,9)

-LB

 

[peechy23]

Awesome!!! Thank you so much. THis worked great. I just added an additional step to conditionally supress if the minimum equaled date(9999,9,9)