Maximum Select Question

[cabrera01]

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

 

[kskid]

Since you stated that employees have either 1/1/1753 or 12/31/2008, then I can safely assume that there is only one {BENEFITS} record per employee and no need for the maximum.

You record select would be

{BENEFITS.STOP_DATE} IN [DATE(1753,1,1), DATE(2008,12,31)]

 

[cabrera01]

Some Employees have both dates and the the majority have only the 1/1/1753. No, unfortunately those employees that have both dates, have multiple records. If an employee has multiple records, I need to be able to ONLY select the 12/31/2008 record, while still retaining the 1/1/1753 for those employees that only have one record.

 

[IanWaterman]

Do as KS says and the group by employee ID. Instead of using detail to display, suppress detail and place fields required in Group footer. Fo the stopdate, just show max summary.

Ian

 

[cabrera01]

I can do that, but the problem is that if I do that and try to calculate the payments, if an employee has 2 records, it will calculate both sums instead of just one.

 

[IanWaterman]

Change the sum to a Running Total and get it to evaluate on change of employee.

Ian

 

[cabrera01]

Here is my problem:

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

I've sorted the Stop Date to be Acending, so I can get the most current value if there are 2 employee records and place the amount in the GF of the Employee.
When I do a sum by department, the totals are wrong. In this example, I'm getting 91000, when in fact I should be getting 87000. This is what I was trying to prevent by only showing one record for employee, even though the employee may have 2 records. I have the running total to Evaluate on each employee and change on departments.

 

[lbass]

Instead of suppression, insert a group on {table.employee} and then go to report->selection formula->GROUP and enter:

{table.benefitsstopdate} = maximum({table.benefitsstopdate},{table.employee})

This will display one record per customer. Then use a running total of cost, that evaluates for each record, reset on change of department, and display the result in the department footer. You MUST use running totals, because non-group selected records contribute to the more usual inserted summaries. With group selection, you don't have to use any special criteria in the evaluation section, although you would have to if you used suppression instead.

-LB