sorting averages in descending order 3

[p658484]

Hello
I am trying to sort the averages in a column (column C) in descending order. These values are calculated fields in the report. I use Crystal 11. Below is the report.

A B C (=A-B)
27.00 8.19 18.81
23.00 5.76 17.24
15.50 6.98 8.53
20.00 8.10 11.90
15.00 4.95 10.05
14.00 4.95 9.05

I would like to sort column C in descending order. Column A and B are group averages. Column C=A-B
thanks

 

[Madawc]

If they are groups, the 'top N' function should do it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[p658484]

Hi Madawc
Thanks for your response. These are calculated averages in the group footer. Not just data which is grouped. This does not come up in the Top N sort.
thanks

 

[Madawc]

Then I can't see a way to do it in Crystal. If you can use SQL in combination with Crystal, you could do the processing in an SQL Command (Stored Procedure). You could then read the result as if it was a table and sort it.




Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You can create the two averages in SQL expressions, use them in the formula that subtracts one from the other, and then place the result in the detail section, insert a maximum on the result at the group level (in order to activate the group sort), and then sort on this maximum. The SQL expression {%avgA} would look something like:

(
select avg(`A`)
from table x
where x.`groupfield` = table.`groupfield`
)

Repeat for field B. Then use a formula like {@diff}:

{%avgA}-{%avgB}

Place {@diff} in the detail section, and follow my earlier suggestion.

-LB