Grouping on a formula

[pzippel7]

CR 8.5 Access db

Within a group, I have three data items;
1. A distinct count of loan numbers
2. Aging (# days) .... >5 is considered late
//@count >5
if {@age group}in 0 to 5 then 1 else 0

where the "age group formula is: Abs ({Retail Closed File Table.OPENDATE}-{Retail Closed File Table.CLOSEDATE})
3. A formula denoting what % of #2 above is of #1
//@% over tolerance

sum({@count >5},{Retail Closed File Table.LFC Name})/count({Retail Closed File Table.FOLDERNUMBER},{Retail Closed File Table.LFC Name})*100

I want to sort by this @% over tolerance formula, but it is not available to do so. I'm new to Crystal and would appreciate any help. Thanks a bunch.

 

[lbass]

If I understand you correctly, I think you will get the correct sort by going to report->topN/group sort->select the {Retail Closed File Table.LFC Name} group tab->select "sum of {@count>5}".

-LB

 

[pzippel7]

Actually, I'm trying to sort it by the % over tolerance fielde (which doesn't show as an option in Top N). Maybe example data will help

Group: ABC Corp
Loan Count # Resolved >5 days % Over Tolerance
10 10 100%
5 2 40%
100 20 20%

I can sort by @count>5 (# Resolved >5days), but it's the % Over Tolerance field I'd like to sort by. Thanks for the reply. Appreciate you offering support.

 

[lbass]

You will need to use a SQL expression to do this. I'm calling Group #1 {Retail Closed File Table.Company Name} and Group #2 (where the percentage you want to sort by is located) (Retail Closed File Table.LFC Name}. Go to the SQL Expression editor and enter the following which I am calling {%percent}:

(select count(AKA.`Company Name`) from Retail Closed File Table AKA where AKA.`Company Name`= Retail Closed File Table.`Company Name` and
AKA.`LFC Name` = Retail Closed File Table.`LFC Name` and
AKA.`CLOSE DATE`- AKA.`OPEN DATE` > 5) / (select count(AKA.`FOLDERNUMBER`) from Retail Closed File Table AKA where AKA.`LFC Name` = Retail Closed File Table.`LFC Name` and
Retail Closed File Table.`Company Name` = AKA.`Company Name`) * 100

You will need to substitute your exact table and field names, but leaving "AKA" as it is, since it represents an alias table name here. Place
{%percent} on your report canvas in the details field. Even though the maximum of this field will be the same as the field itself, you need to insert a maximum so that there is a summary available for the topN sort. So the next step is to insert a maximum on {%percent}. Then go to report->topN/group sort->LFC Name tab->select
"maximum of %percent".

This worked when I tested it here. Creating SQL expressions can vary depending upon datasource, etc., so you might have to play with this. In particular, I am unsure of how a table name with spaces in it (as yours has) is handled in a SQL expression.

-LB