Find the difference between 2 rows in grouped data that is restrained by time

[cengleman82]

I'm trying to figure out a formula that will assist with a fleet gas report. The report displays data as follows:

Vehicle #|Date of Fillup|Amt of Fuel|Odometer at fillup|Difference|MPG
1 |12/31/12| 10.00 | 30,000 | __ | 50
1 | 1/1/13 | 10.00 | 30,500 | 500 | 50
1 | 1/15/13 | 10.00 | 40,000 | 500 | 50
1 | 1/30/13 | 10.00 | 40,500 | 500 | 50
1 | 2/4/13 | 10.00 | 50,000 | 500 | 50

So, I'm trying to compute the difference field, where it takes the current odometer reading and subtracts the previous value to see how many miles the car has traveled since the last filling. I've tried using the previous function, which works ok, except for the very first value. The reason the first value messes up is because I also need to restrain the record data between 2 date parameters. Therefore, in the above example, if I set the date to be between 1/1/13 and 1/31/13, there would be no previous odometer reading for the record on 1/1/13 to reference.

Any ideas?

 

[CoSpringsGuy]

if you are grouping by vehicles this is going to have to be thought through again as I don't think your current formula will work for the second vehicle...
but in case there is only one vehicle for your report try this
create two date parameters parameterfromdate & parameterthroughdate

you could us the following for record selection // this would select all dates up to your through date
{fillupdate} <= {parameterthroughdate}

then in record suppression use this formula // this would suppress the previous dates including the one with the most current odometer reading but it would still be available for your first calculation
{fillupdate} <= {parameterfromdate}



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[cengleman82]

CoSpringsGuy,

Thank you! That works well. I do have the vehicles grouped, but I tested it out and it works fine for all of them. I knew there had to be some way of having it pull most all of the data, but not displaying all of the data, I just couldn't put my finger on it until you mentioned record suppression.

Thanks again.

 

[cengleman82]

So one more caveat. Any ideas how to total this new "difference" column? I've read and tested some 3 part variables with no luck. I don't know if it is because of the suppression techniques involved or not. I really just need to find the differences between the odometer readings, total them, and then supress the details section and just see the totals in the group footer. I'm about ready to submit; I'm not sure this one is worth the trouble.

 

[CoSpringsGuy]

submit??? Never!!!
lets start over with your fields Vehicle #|Date of Fillup|Amt of Fuel|Odometer at fillup

I recreated this in an excel spreadsheet because it was better than watching the Bachelor with my wife She thinks Im hard at work but hanging out with her at the same time

Make sure your report is grouped by vehicles and records sorted by date, secondary sort by odometer ascending in case there are more than one fill ups on the same date
place your fields in the order you indicated in your original post

create a parameter field called daterange
Type is date
in options select allow range values
NOTE: In the following formulas you will need to substitute your database fields for mine. Sorry for stating the obvious.

create a formula called accumulator and place next to odometer at fill up in details section

numbervar miles;
numbervar milesum;
numbervar mpg;
numbervar fueltot;
//whileprintingrecords; 
if {Sheet1_.Date of Fillup} >= minimum({?daterange}) then
(
    if {Sheet1_.Vehicle #} = previous({Sheet1_.Vehicle #}) then
    (
    miles := {Sheet1_.Odometer at Fillup} - previous({Sheet1_.Odometer at Fillup});
    milesum := milesum + miles;
    mpg := miles/{Sheet1_.Amt of Fuel};
    fueltot := fueltot + {Sheet1_.Amt of Fuel};
    )
    else 
    (
    miles := 0;
    milesum := 0;
    mpg := 0;
    fueltot := 0;
    );
)
else
(
miles := 0;
milesum := 0;
mpg := 0;
fueltot := 0;
);
miles

create another formula called mpg and place this next to the previous formula

numbervar mpg;
whileprintingrecords;
mpg

Create another formula called total miles and place this in the vehicle group footer under the accumulator formula

numbervar milesum;
whileprintingrecords;
milesum

create another formula called avgmpg and place this in vehicle group footer under mpg formula (this one wasnt part of your request but it seemed logical to create it)

numbervar avgmpg;
numbervar milesum;
numbervar fueltot;
whileprintingrecords;
avgmpg := milesum/fueltot

go ahead and run the report now or even after placing each formula on the report to see if you are getting the results you are wanting.

Now we need to suppress the records you dont want so in the section expert - details - enter this formula to suppress

{Sheet1_.Date of Fillup} < minimum({?daterange})

last thing (probably should have been the first but doing it this way shows you how the whole thing works) add this code to the record selection expert.

{Sheet1_.Date of Fillup} <= maximum({?daterange})

add/change your own column names...

YAY!!! That was fun.....

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[cengleman82]

I'm glad my wife doesn't like those shows, but I admire your methods to get out of watching.

I can't thank you enough for what you've done so far. I'm going to tell everyone I couldn't have done it without CoSpringsGuy, but most likely I'll get all the glory.

Anyway, everything works except for one last thing. All of the totals, or formulas we (you) calculated, is really all I need on the report. However, if I try to hide the details section, the accumulator formula throws a divide by 0 error. I assume this is because it needs to see the values on the report in order to do any calculations. Is there a way to accomplish this another way? I don't want to sound helpless here, but in the 3 years I've been messing with CR, this is the first time I've ever had to create anything this involved (for a rather simple task). Any help you can provide would be very much appreciated.

 

[CoSpringsGuy]

hmmmm ... odd... when I hide the details section on the report I created I dont get the same error... You will get that error if you try and move the "accumulator" formula in the group footer.. Is that what you are doing?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[cengleman82]

No. I have the accumulator in the details. The error kicks back to the avgmpg formula. It is the only one that won't work with the details hidden. I placed it in the vehicle # group footer. I even recreated the entire report from scratch to make sure that there wasn't something left over from my original attempts causing problems.

 

[cengleman82]

I got it. It was telling me exactly what was wrong, but I guess I'm kind of dense.

I revised the avgmpg statement to read as follows:

numbervar avgmpg;
numbervar milesum;
numbervar fueltot;
whileprintingrecords;
if fueltot > 0 then
(
avgmpg := milesum/fueltot
)
else
(
avgmpg := 0
)

We have some vehicles that sit here for forever and a day before they are driven, which would make it easy for a car to not have any gas put in it for the month long period I am examining. But it works now. Thanks for your help on this. I appreciate it.

 

[CoSpringsGuy]

Glad you got it working!

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection