asked on

Crystal Reports sum one field based on the top three entries in another field.

I am a Crystal Reports novice. I have an existing report that looks at a table of all employee paychecks. It sums up the hours worked field by all employees on the last paycheck date (by referencing the maximum paycheck date), so that we can then calculate the number of full-time equivalents on that paycheck. Management would like me to update this report to have the same FTE calculation over the previous three paycheck dates. I tried using the running total field below. The formula that is used under Evaluate is this:

{PR_Check_Exp_Distrib.Check_Date} in [(NthLargest (1, {PR_Check_Exp_Distrib.Check_Date})), (NthLargest (2, {PR_Check_Exp_Distrib.Check_Date})), (NthLargest (3, {PR_Check_Exp_Distrib.Check_Date}))]

When I use this, it only gets the maximum check date. Like I said, I'm a novice, if anyone can point me in the right direction, I'd really appreciate it.

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ejscn

ASKER

Thank you, James. I checked what the report is pulling, and it doesn't seem to be restricting to just the most recent check date. I may end up abandoning this idea and getting the report a different way. I really appreciate the time you put into responding.

James0628

If you wanted to pursue this further, you could start by creating three formulas with just NthLargest (1, {PR_Check_Exp_Distrib.Check_Date}), NthLargest (2, {PR_Check_Exp_Distrib.Check_Date}), and NthLargest (3, {PR_Check_Exp_Distrib.Check_Date}), and then put those formulas on the report and see what you get.

James