We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Group Max Selection Question

Medium Priority
234 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Commented:
To create subtotals where you are suppressing records that contain values you don't want to include, you can use Running Totals fields.  Using normal Summary totals will include the suppressed records.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Agree.

I assume you are grouping by the person
You could also then sort by the date and display the data in the group header.

The running total could be set to evaluate on change of group

mlmcc
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.