Metalteck
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.