4 Replies Latest reply on Feb 20, 2012 10:55 AM by jason.delooze

    Calculating average mileage

    Sukisu

      Hi, I have table with the mileage readings for various cars taken on various dates, (not all taken on the same date). I want to work out the average mileage for each car used between each date that particular cars mileage was recorded, can anyone help with a formula that would do this for me?

        • 1. Re: Calculating average mileage
          jason.delooze

          Let's assume you have data for Car A on Date1 with Mileage1 and on Date2 with Mileage2.  Then the average mileage (per day) for Car A over the time period Date1 to Date2 would be

           

          (Mileage2 - Mileage1) / (Date2 - Date1)

          • 2. Re: Calculating average mileage
            Sukisu

            Thanks for your response Jason,  currently I have Date1 and Date2 in different records within the same field, and likewise with Mileage1 and Mileage2.  How do I find the two nearest dates for a particular car, and do the calculation on those records?

            • 3. Re: Calculating average mileage
              comment

              su.knight@torld.co.uk wrote:

               

              Hi, I have table with the mileage readings for various cars taken on various dates, (not all taken on the same date).  I want to work out the average mileage for each car used between each date that particular cars mileage was recorded, can anyone help with a formula that would do this for me?

               

              Define the following summary fields:

              • MinDate = Minimum of Date;

              • MaxDate = Maximum of Date;

              • MinMiles = Minimum of Mileage;

              • MaxMiles = Maximum of Mileage;

               

              and a calculation field =

               

              ( GetSummary ( MaxMiles ; CarID ) - GetSummary ( MinMiles ; CarID ) ) /

              ( GetSummary ( MaxDate ; CarID ) - GetSummary ( MinDate ; CarID ) + 1 )

               

               

              Sort the records by CarID.

              1 of 1 people found this helpful
              • 4. Re: Calculating average mileage
                jason.delooze

                What is your goal here - to calculate the average mileage per day for each time period for each car - or a single average mileage for each car (taken over the entire set of mileage records for the car)?  Example:

                 

                Car1 - 1/18/2011 - 14265

                Car1 - 1/25/2011 - 14538

                Car1 - 2/22/2011 - 15852

                Car1 - 3/14/2011 - 16207

                 

                For Car1 over the individual time periods

                 

                1/18/2011 - 1/25/2011 - 39.0 miles per day

                1/25/2011 - 2/22/2011 - 46.9 miles per day

                2/22/2011 - 3/14/2011 - 17.8 miles per day

                 

                or over the entire time period

                 

                1/18/2011 - 3/14/2011 - 35.3 miles per day

                 

                Of course, the implementation would depend on your database design - what Tables (entities) you have and what attributes (fields) in each table (entity).  If we assume you have a Mileage table with attributes CarID, Date, Mileage, then a self-join between a table occurrence (Mileage) of the Mileage table with another table occurrence (OrderedCarMileageGroup) of the Mileage table defined as

                 

                Mileage::CarID = OrderedCarMileageGroup::CarID

                and

                Mileage::Date < OrderedCarMileageGroup::Date

                 

                and sorted by OrderedCarMileageGroup::Date (ascending order)

                 

                will allow Mileage record to know which Mileage record is next (by date) Mileage record for that Car.  The Average mileage per day follows easily:

                 

                AverageMileage =

                   Case(

                      not IsEmpty( OrderedCarMileageGroup::CarID )  // does the next mileage record exist?

                         ( OrderedCarMileageGroup::Mileage - Mileage ) / ( OrderedCarMileageGroup::Date - Date )

                   )

                 

                Hope that helps.