Mileage calculations

[Pcullc]

I have 3 users on appsheet. We are using it has an expense reporting. When they put in an expense, and they pick the “Gas” category, I have it asking for the mileage on the vehicle. Now, what I need, is the ability to automatically calculate the miles since the last fill up, so when that is in place, I can put in place the formulas/expressions that will calculate the MPG for me. I’ve tried every thing, and can’t get it to do it…

[Kirk_Masden]

Hi @Pcullc! It sounds like it should be relatively easy to work out the mileage, especially if everyone is filing up the tank whenever they put gas in and you all are using the same vehicle. I take it that the part you are having trouble with is calculating the difference between last time and this time. “This time” is easy but “last time” may be a bit more complicated on AppSheet than on a spreadsheet. Here’s how I would do it. If my approach isn’t the best, I hope someone else will chime in.

Let’s say that your table name is Expenses and you’ve got columns named Gas, Odometer, and Date. The way I would find the previous Odometer reading is by first making the following SELECT() expression:

SELECT(Expenses[Odometer], ([Date] < [_THISROW].[Date]))

This makes a list of all of the odometer readings that came before the odometer reading of the current row. Now, if you find the largest of these odometer readings, you’ll have the previous odometer reading.

MAX(SELECT(Expenses[Odometer], ([Date] < [_THISROW].[Date])))

Then, you can subtract that from the current odometer reading to find the number of miles driven:

[Odometer] - MAX(SELECT(Expenses[Odometer], ([Date] < [_THISROW].[Date])))

There’s more to do to finish the calculation but I wonder if this is enough to get you past the point you were stuck on.

Good luck!

[Pcullc]

Thanks so much Kirk. I will start with that and see where it goes. I think the only problem I will have is filter it by vehicle, since all three different vehicles have different mileage. I will work on that and see where it gets me. Thanks for the reply!

[Kirk_Masden]

Good luck! Filtering by vehicle is also possible. Let’s say you have another column called “Vehicle”. Your SELECT() expression might be as follows:

SELECT(Expenses[Odometer], 
   and(
	   ([Date] < [_THISROW].[Date]),
	   ([Vehicle] = [_THISROW].[Vehicle])
	   )
     )

Of course, the spacing is just to make it easier to see the structure of the expression.

I hope I got the expression right. I haven’t tested it but I think it should be OK. If not, I hope someone will set me straight.

[Pcullc]

That totally works now that I combined them. Thanks Kirk!