Crystal 8 - Calculating SUM (Max Value within Groups)

[Chinnymerlgrove]

I need some help calculating the sum of a group maximum values.
This is what I have…

Group 1 - Group data by EMPLID
Group 2 - Group by Company Code
For each company, the employee has accumulated monthly earnings. So to get the maximum earnings per company I do step 3.

Step 3 - Insert Summary of the Maximum Gross YTD Salary by Group 2 The fromula I am using is as follows:
((Maximum ({PS_EARNINGS_BAL.GRS_YTD},({PS_EARNINGS_BAL.COMPANY}) )))

I tried writing a formula to get the total for Duck, Donald based on Step 3. I cannot do a regular SUM function as it will total all the monthly earnings which would make the total inflated. The result should be as follows for employee Donald Duck.

Maximum Gross YTD for Co 1 = $6,700 +
Maximum Gross YTD for Co 2 = $8,760
= $15,460

My formula is as follows Sum (Maximum ({PS_EARNINGS_BAL.GRS_YTD}, {PS_EARNINGS_BAL.COMPANY}),({PS_EARNINGS_BAL.EMPLID}) )

My formula produces an error that says “Summary/Running Totl field could not be created".

 

[lbass]

You need to use a variable. Create three formulas:

//{@reset} to be placed in GH1:
whileprintingrecords;
numbervar summax;
if not inrepeatedgroupheader then
summax := 0;

//{@accum} to be placed in the GF2:
whileprintingrecords;
numbervar summax := summax + Maximum ({PS_EARNINGS_BAL.GRS_YTD},{PS_EARNINGS_BAL.COMPANY});

//{@display} to be placed in the GF1:
whileprintingrecords;
numbervar summax;

-LB

 

[synapsevampire]

To get the sum of the maximums, change your formula to also include a variable, as in:

whileprintingrecords;
nmbervar TotalMax:=TotalMax + ((Maximum ({PS_EARNINGS_BAL.GRS_YTD},({PS_EARNINGS_BAL.COMPANY}) )));
((Maximum ({PS_EARNINGS_BAL.GRS_YTD},({PS_EARNINGS_BAL.COMPANY}) )))

A bit of a language barrier here, but I think that you'll need 2 other formulas as well:

Company Code Group Header:

whileprintingrecords;
nmbervar TotalMax:=0

Company Code Group Footer:

whileprintingrecords;
nmbervar TotalMax;

The latter is where you display the totals.

And please show the underlying data if you're going to show what the output should be, we have no way of knowing how you arrive at it.

-k

 

[synapsevampire]

Ooops, LB had the level of where the formulas should exist correct, I incorrectly wrote it for 3 groupings.

-k

 

[Chinnymerlgrove]

Thank you for these quick responses. I have never used variables before, so I will go try it now. Per synapsevampire's suggestion, below is the underlying data that generated the total.

ID NAME CO Month ERNCD GRS YTD
000036 $15,460

000036 RND 9 RE1 $6,700.00
000036 Duck,Donald RND 9 RE1 $6,700.00
000036 Duck,Donald RND 8 RE1 $3,875.00
000036 Duck,Donald RND 7 RE1 $3,698.00
000036 Duck,Donald RND 6 RE1 $3,100.00
000036 Duck,Donald RND 5 RE1 $2,500.00
000036 Duck,Donald RND 4 RE1 $1,500.00
000036 Duck,Donald RND 3 RE1 $700.00
000036 Duck,Donald RND 2 RE1 $600.00
000036 Duck,Donald RND 1 RE1 $300.00

000036 RND 9 RE2 $8,760.00
000036 Duck,Donald RND 9 RE2 $8,760.00
000036 Duck,Donald RND 8 RE2 $8,000.00
000036 Duck,Donald RND 7 RE2 $7,500.00
000036 Duck,Donald RND 6 RE2 $5,600.00
000036 Duck,Donald RND 5 RE2 $4,000.00
000036 Duck,Donald RND 4 RE2 $3,000.00
000036 Duck,Donald RND 3 RE2 $2,000.00
000036 Duck,Donald RND 2 RE2 $1,000.00
000036 Duck,Donald RND 1 RE2 $500.00

 

[Chinnymerlgrove]

I copied lbass' codes (exactly as given) into three formulas and placed them where indicated and IT WORKED!

Thanks so much!