Link to home
Start Free TrialLog in
Avatar of Metalteck
MetalteckFlag for United States of America

asked on

Group Max Selection Question

I have a report in crystal XI where I need to calculate the payments that the company has to pay for all its employees.
If an employee is active, their {BENEFITS.STOP_DATE} = 1/1/1753. Unfortunately there are some employees that have either 1/1/1753 or 12/31/2008.  The majority of records has the end date as 1/1/1753, but for those that have both, I need to select the 12/31/2008 record. I wrote this formula:
{@Check}:
if {BENEFITS.STOP_DATE} = 1/1/1753 then 1 else
if {BENEFITS.STOP_DATE} = 12/31/2008 then 2 else 0

I then would like to use the select expert to select the maximum value of {@Check}, but I can't seem to find the correct way to do this.

Any suggestions would be helpful

Thanks
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

I don't see how you can do this by record selection.
You will have to use a conditional suppression formula on records in the report to eliminate the umnwanted records.

Sort the records by Stop_date within Employees id; then you could do...

If not onlastrecord and {benefits.employeeid}=next({benefits.employeeid}) then
true   //suppress this record
else
false

This formula does assume that you cannot have two records for the same employee both with a date of 1/1/1753.
Avatar of Metalteck

ASKER

Thanks, this helps me supress the unwanted records, but when I try to calculate the payments, the totals are all wrong.
Ex: Before Suppression

Name        Stop Date    Cost   Check
Joe Smith   1/1/1753    48000    1
Joe Smith  12/31/2008   46000    2
GF                      46000
Jane Smith  1/1/1753    43000    1
Jane Smith 12/31/2008   41000    2
GF                      41000
Subtotal               91000

After Suppression:
Name        Stop Date    Cost   Check
Joe Smith  12/31/2008   46000    2
GF                      46000
Jane Smith 12/31/2008   41000    2
GF                      41000
Subtotal               91000
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial