Formula in CR 8.5

[geo40]

Hello,

In a table for a certain car I've got following fields:

Date Qty. petrol(ltr) Amount (H)Odomtr
2002/04/01 52,2 53,24 15500
2002/04/11 47,4 46,45 16069
2002/04/15 53,4 54,20 16843
2002/04/23 29,3 30,47 17136
2002/05/02 48,4 49,61 17867
2002/05/06 49,6 51,09 18497

In our company we've almost fifty cars. In one Crystal Report ( if possible the CrossTab) I want to see for each car how many kilometres will be driven with one litre of petrol in the given periode from 2002/04/01 till 2002/05/06 ? Can somebody help me with the correct formula(s)? If necessary I can give some additional information.

Gr. Jeroen.

 

[kenhamady]

1) Select the records in the date range
2) Group by Car
3) Sum the Petrol used for each car
4) Calculate the miles for each car (not sure from your sample how to do that. Can we sum the amount or do we need to take the lowest and highest miles in the period?)
5) Divide the miles total by the Gallon total
6) Hide the details and group header Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[geo40]

Thanks for responding so quickly Ken.

(4) What I want to do is to subtract the maximum of the miles in the period from the minimum of the miles in the period so I will get the driven miles in the period. With which formula can I do this ?

(5) Then I want to divide the driven miles by the sum of the Petrol used in the period. With which formula can I do this ?

GEO40

 

[kenhamady]

If you are grouped by vehicle the formula would be something like:

( Maximum ({odom},{veh}) - Minimum ({odom},{veh}) ) /
Sum ( {petrol},{veh} )

To be precise, you might want to think about the first petrol figure in the period. If that is the date of fillup, then that is for mileage before that date. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[geo40]

Hello Ken,

The formula you suggest works. Thank you for that. The remark you added in your last mail is right.

So when I go back to my example I have to take the difference between the maximum and minimum total driven miles in the period. This calculation I've to divide by the total quantity of petrol in the period( 52,2 + 47,4 + 53,4 + 29,3 + 48,4 = 230,7 liters). As you see I exclude the last item ( 49,6 ), because from that row I only want to know the total driven miles. How would I manage this in my CR ???

Greetings GEO40

 

[kenhamady]

Do you want to exclude the last item or the first item? I am assuming that the last record is telling me that I drove 630 and then filled up with 49,6 - so I assumed that you would need to strike the first record's petrol.

The simple way to strike the first or last record is to write:


( Maximum ({odom},{veh}) - Minimum ({odom},{veh}) ) /
( Sum ( {petrol},{veh} ) - {petrol} )

If this is placed in the group header it will subtract the first petrol number. Placed in the group footer it will subtract the last petrol number. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com