Group Max Selection Question

Posted on 2009-02-10
Medium Priority
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:
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

Question by:metalteck
  • 2
LVL 77

Expert Comment

ID: 23609059
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

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

Author Comment

ID: 23613064
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
LVL 77

Accepted Solution

peter57r earned 1000 total points
ID: 23613462
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.
LVL 101

Assisted Solution

mlmcc earned 1000 total points
ID: 23615125

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


Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month17 days, 13 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question