Difference between first and last record in a group

[Beez123]

My posts seems to have disappeared, so I will post this again... Using CR 8.5:

I have test scores grouped by student and sorted by date
I know I can get the first and last score displayed by putting the score field in the header and footer, however, how can I access those two values to use in a formula? I want to display the difference between the first score and the last score, but I also want to display all the scores in between, e.g.:

John - Improved by 20 points
1/1/2005 50
2/1/2005 55
3/1/2005 70

Jane - Improved by...

How do I calculate and display the number in bold?

Thanks.

 

[lbass]

First group on student. Then create three formulas:

//{@reset} to be placed in the group header;
whileprintingrecords;
numbervar min := 0;
numbervar max := 0;

//{@accum} to be placed in the details section:
whileprintingrecords;
numbervar min;
numbervar max;

if {table.date} = minimum({table.date},{table.student}) then min := {table.score};
if {table.date} = maximum({table.date},{table.student}) then max := {table.score};

//{@display} to be placed in the group footer:
whileprintingrecords;
numbervar min;
numbervar max;
max - min

-LB

 

[Beez123]

One more thing... what if I want to get an average of these difference values across all students? When I right click on the @display field, I don't have the option to 'Insert Summary Operation'.

Thanks again for your help.

 

[IdoMillet]

Here is an alternative approach to the original question:

Assuming the records are sorted by {score_date} within the {student} group:

1. Create a formula {@Combo} such as:

{score_date} & "::" & {Score_Field}

2. Get the difference between the last and first scores using:

Split(Maximum({@Combo}, {Student}), "::")[2] – Split(Minimum({@Combo}, {Student}), "::")[2]

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Change the display formula to:

//{@display} to be placed in the group footer:
whileprintingrecords;
numbervar min;
numbervar max;
numbervar sumdiff;

sumdiff := sumdiff + max - min;
max - min

Then in the report footer add a new formula:

//{@ave}:
whileprintingrecords;
numbervar sumdiff;

sumdiff/distinctcount({table.student})

-LB

 

[Beez123]

Thanks LB, I think I'm getting the hang of it now.