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โฆ
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!
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!
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.
That totally works now that I combined them. Thanks Kirk!
User | Count |
---|---|
42 | |
34 | |
26 | |
23 | |
15 |