Minimum if meets criteria

[SJMcAbney]

I would like to calculate the minimum value in a group but only if a certain field in that group has a value.

i.e.

Group, InvoiceDate, Earned

001, 1st Jan 2005, £0
001, 1st Mar 2005, £0
001, 1st May 2005, £0
001, 1st Jun 2005, £45.32
001, 1st Jul 2005, £0
002, 1st Jan 2005, £0
002, 1st Feb 2005, £28.97
002, 1st Apr 2005, £0

I would like to be able, given that a criteria/parameter is 1st May 2005 to determine if this is OLD or NEW business. OLD business is any invoice that makes money prior to the criteria date and NEW is the invoice that makes money equal to or after the criteria date. I'd like to do this on a group level so that for Group 001 (see above) the minimum date would be 1st Jun 2005 and for Group 002 the minimum date would be 1st Feb 2005.

Does anyone have any ideas on how I would accomplish this? I'd have thought I could have used a criteria in the Minimum function like the following, although it doesn't seem possible on looking at the help files.

Minimum({TableName}.{FieldName}, {GroupName}, {InvoiceField} > 0 )

Thanks for taking the time to read this.







Stewart J. McAbney | Talk History

 

[Madawc]

Try two running totals, one for all members of a group, and the other with an 'Evaluation Formula' that excludes records with Earned=0. From this, you should be able to work it out in the group footer. Group details can be suppressed.


Madawc Williams (East Anglia, UK)

 

[SJMcAbney]

Thanks. Will try that.

Stewart J. McAbney | Talk History

 

[lbass]

Why not eliminate the zero fields by using a record selection formula like:

{table.invoice} > 0

You could then insert a group on {table.group}. Then create a formula:

if {table.date} < {?date} then "Old" else
"New"

Insert a group on the formula and then right click on the date field in the detail section and insert a minimum at the group levels where you want the minimum.

-LB