Calculate mileage between minimum and maximum dates

[Laurelin]

I am trying to calculate the mileage of a vehicle that has a number of meter entries in a table. What I need to do is pull out the first and last entry in that table and calculate the mileage driven of the unit by subtracting the first mileage from the last.

My data looks like this:

UNIT MILEAGE DATE
KN5644 34,560 3/4/2008 1:20:56 PM
KN5644 36,345 5/2/2008 3:50:34 PM
KN5644 40,345 8/9/2008 5:23:44 PM
KN5644 55,345 7/3/2009 2:34:56 PM
KN5644 70,555 2/2/2010 7:34:33 PM

I want to be able to calculate the mileage driven between the first period and the last period on a single line. (70,555 - 34,560 = 35,995)

Any help would be appreciated!

 

[lbass]

Insert a group on {table.unit}, sort on mileage (which appears to be cumulative) and then create a formula like this:

maximum({table.mileage},{table.unit})-minimum({table.mileage},{table.unit})

You can place this in the unit group header and then suppress the detail section if you wish.

-LB

 

[Laurelin]

Oh, I should have mentioned. For some of the units there are some bad mileage entries. I did exactly what you suggested at first but then I realized that there are some bad data entries involved. However the first and the last entry always seem to be correct.

It might look like this:

UNIT MILEAGE DATE
KN5644 34,560 3/4/2008 1:20:56 PM
KN5644 36,345 5/2/2008 3:50:34 PM
KN5644 40,345 8/9/2008 5:23:44 PM
KN5644 423,455 10/3/2008 3:45:23 PM
KN5644 55,345 7/3/2009 2:34:56 PM
KN5644 70,555 2/2/2010 7:34:33 PM

 

[lbass]

I see no reason to believe that the first and last would be exempt from mistakes since at one time the "bad" mileage was found on the last date. By what criteria are you determining a mileage is "bad"? The simplest solution would be to fix the data before reporting on it.

-LB

 

[Laurelin]

I wish I had the ability to fix the data issue with this application but we do not own it and all the vendor tells us is "we are considering our options". That has been going on for too long. :/

Is there any method in which I can compare the first and last mileage entries for a unit? If there is a unit that is out of wack I will notice it and be able to manually correct it on my spreadsheet. But there are way too many to do that for all of them.

I know that the first entry is always correct. It was imported in from the previous application. From my research the last entry is correct 99% of the time and I feel comfortable reporting off of it.

 

[fisheromacse]

in your example data, is there a 'bad' entry?
what might 'bad' entries look like?

 

[lbass]

Create three formulas:

//{@reset} (suppress) for the unit group header:
whileprintingrecords;
numbervar first;
numbervar last;
if not inrepeatedgroupheader then (
first := 0:
last := 0
);

//{@accum} for the detail section (suppress):
whileprintingrecords;
numbervar first;
numbervar last;
if {table.date} = minimum({table.date},{table.unit}) then
first := {table.mileage};
if {table.date} = maximum({table.date},{table.unit}) then
last := {table.mileage};

//{@result} for the unit group footer:
whileprintingrecords;
numbervar first;
numbervar last;
last-first

-LB

 

[Laurelin]

A bad entry would be one where way too much mileage was entered. Such as the forth one in this example of the data. If I do a formula using the maximum of that field then I end up pulling the value in the bad data record.

UNIT MILEAGE DATE
KN5644 34,560 3/4/2008 1:20:56 PM
KN5644 36,345 5/2/2008 3:50:34 PM
KN5644 40,345 8/9/2008 5:23:44 PM
KN5644 423,455 10/3/2008 3:45:23 PM
KN5644 55,345 7/3/2009 2:34:56 PM
KN5644 70,555 2/2/2010 7:34:33 PM

 

[Laurelin]

Going to give that a shot, thank you Lbass. I know you are a master at Crystal Reports. I hope someone pays you well for it.

 

[fisheromacse]

lbass' formula should do the trick, but if you want to check for excessively large entries, the below should work for all but the last record (the one equal to the max date)

since you already sort by Unit and by date, you could do a logical check to determine if the next mileage is less than the current mileage, thus indicating a potential error on the current record.

something similar to:
IF {table.Mileage}>next(table.Mileage) THEN "ERRor" else "O.K.";

 

[Laurelin]

It works great. Thank you again Lbass!