Remove 20% of the highest value

[ejerblom]

Hi,

I would like to make the following calculation in my report:

I have a list of values where I would like to remove 20% of the highest values. So, anyone who knows how to remove 20% of the highest values from the list?

Regards
Ulrika

 

[Madawc]

In Crystal 8.5, I would fist use the 'maximum' option in a running total or summary total, to get the highest value. Then use a formula field
@your.max * 0.8.

If it is more than one highest value, that would be more complex. What criteria do you want used?

Madawc Williams
East Anglia, Great Britain

 

[ejerblom]

I need to remove 20% of the values and the 20% that I remove must be the ones with highest value. For example if I have 20 different values I need to remove the 4 highest values. Is it possible?

Ulrika

 

[lbass]

You haven't explained whether these are summary values or detail level values. If summary values, then go to report->topN/group sort and insert a topN on them in descending order. Then, if you only have one group, you can go to format->section->group header or footer (wherever you have the summaries)->suppress->x+2 and enter:

groupnumber < 5

If you have more than one group, you would need to create a running total to manage the suppression of group level summaries.

If the value is at the detail level, then go to report->sort records and add the value as the sort field, descending. Then go to format->section->details->suppress->x+2 and enter:

recordnumber < 5

If you have groups and you are suppressing details within groups, you would need to create a running total that resets on change of group.

-LB

 

[IdoMillet]

Supress the detail section using a formula such as
-------------------------------------
{Value} > PthPercentile(80, {Value})
-------------------------------------

Cheers,
- Ido


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

http://www.MilletSoftware.com

 

[ejerblom]

Thanks all for your help but I need some more.

Well, it did work, 20% of the highest values were removed. But I still have a problem. I need to count and summarize the records that is left after the removal of 20%. Can it be done? I have no group.

/Ulrika

 

[lbass]

To be more general, my earlier suggestion should be amended to:

recordnumber < round(.2 * count({table.ID}),0)

To count the remaining records, you could create a formula:

count({table.ID) - round(.2 * count({table.ID}),0)

For other summaries, you could use two formulas like the following:

//{@amount} for the details section:
whileprintingrecords;
numbervar amt;

if recordnumber >= round(.2 * count({table.ID})) then
amt := amt + {table.amount};
amt;

//{@displayamt} for the report footer:
whileprintingrecords;
numbervar amt;

You can suppress the detail formula.

-LB

 

[IdoMillet]

It's not clear which approach you ended up using (several were suggested above).

Cheers,
- Ido

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

http://www.MilletSoftware.com