Eliminating outliers from data

[arodri]

Hello,

There are a couple different parts to this report that I should explain before asking my question, so I'm sorry if this post is really long!
I have data in a SQL view that looks like this:

Code Date Item Milage
01BZ 1/6/2011 FS Lube 63,251

01KW 4/26/2010 FS Lube 72,883
01KW 10/19/2010 FS Lube 82,226

084C 2/13/2010 Synthetic Lube 3,594
084C 6/7/2010 Synthetic Lube 11,014
084C 9/24/2010 Synthetic Lube 20,669
084C 12/21/2010 Synthetic Lube 26,832

15JY 2/17/2010 FS Lube 209,198
15JY 4/9/2010 FS Lube 212,578
15JY 6/2/2010 FS Lube 216,148
15JY 8/12/2010 FS Lube 22,688
15JY 9/25/2010 FS Lube 224,229
15JY 10/27/2010 FS Lube 228,162

I had a lot of help (from these forums) in order to take the FIRST and LAST record from each code (not min and max) and get the difference of the two. My only group in this report is "code" and I've suppressed the group header and detail sections. Below are the formulas I used to come up with the difference of the first and last record, and also the sum of all of those difference (which I put in the report footer):


Details Section:

whileprintingrecords;numbervar array v_mileage;redim preserve v_mileage[ubound(v_mileage)+1];v_mileage[ubound(v_mileage)] := {MileageField};

Group footer formula:

whileprintingrecords;numbervar array v_mileage;numbervar v_total;v_total := v_total + (v_mileage[ubound(v_mileage)] - v_mileage[2]);v_mileage[ubound(v_mileage)] - v_mileage[2]

Report footer formula:

whileprintingrecords;numbervar v_total;v_total

Reset the variable in the group header:

whileprintingrecords;numbervar array v_mileage;redim v_mileage[1];0

All of this worked great (thanks briangriffin!) and did exactly what I needed it to do. Here is my question:

There are a lot more user errors than we originally thought. Since it's taking the difference of the last record and the first records, I'd like to limit the results to only the records with differences of more than 0 or less than 50,000.

For example, right now (because of user error) I have a code with a difference of: -18,087.
The user typed in the wrong values for the later date, so we get a negative number for the difference.
I also have the same thing happening but with numbers that are too big (in the millions) and this is throwing off my average (After I got the total for everything, I did an average).

Is there a way to eliminate these outliers and just leave the values for differences between 0 and 50,000?


Thanks so much for anyone that can help!