Eliminating bottom and top 5%

[dacards]

I've got a report where I pull tickets for any given month and then report various metrics based on those tickets. I'd like to filter out tickets from the high end and low end of MTTR (Mean Time To Repair), to get a potentially more accurate average.

@MTTR is a formula based on 'Create Date' and 'Resolved Time'. Using @MTTR, how do I tell the report to eliminate the bottom and top 5%?

I'm using Crystal 9.

 

[lbass]

Please show the content of your formula.

-LB

 

[dacards]

@MTTR =
({MOD_TRB_Trouble.Ticket Resolved Time}-{MOD_TRB_Trouble.Event Start Time})*24

Basically gives me a MTTR in hours. So I then have a set of data that could be X number of tickets. When I average the whole, I do not want to count the top and bottom 5% MTTRs.

Thanks and let me knwo if there is anything else i can provide to clarify my request.

 

[lbass]

First add {@MTTR} as your record sort field. Then create two formulas:

//{@accum} for the detail section:
whileprintingrecords;
numbervar hrs;
numbervar tickets;

if recordnumber > .05*distinctcount({table.ticketID}) or
recordnumber < .95*distinctcount({table.ticketID}) then
(
hrs := hrs + {@MTTR};
tickets := tickets + 1
);

//{@Display} for the report footer:
whileprintingrecords;
numbervar hrs;
numbervar tickets;
if tickets <> 0 then
hrs/tickets

-LB