Cannot use Maxrow() to get the desired last value

[Light_newbie]

Hi. I am building an Appsheet to record the odometers of my company's cars and the number of km each car runs per day. 

It contains 4 columns in the Table_Record: [CarName], [Date], [Odometer], [KM Run].

What I'm trying to do is,

•  Enter the car's [Odometer], and the [KM Run] can calculate the difference between the current [Odometer] and the last value of the [Odometer]. (Goal A)
• Furthermore, it should match to the same Car to get the correct last value. (Goal B)
• The final step is to match with the same day. (Goal C)

However, I am having trouble in the very first step. I have added a new virtual column [Latest Odometer]. Using this formula: 

Maxrow("Table_Record", "Odometer", [CarName]=[_Thisrow].[CarName]

And the formula in [KM Run] is 

[Odometer]-[Latest Odometer]

But the result of the [Latest Odometer] is not something I expected: 

CAR01: 11/07/2024 : 2000

Look like it is the _ComputedKey with the following expression: 

CONCATENATE([CarName],": ",[DATE],": ",[Odometer])

Since I clicked [CarName], [DATE] and the [Odometer] as the key and it auto-computed this virtual column for me. This time I only checked the [Odometer] as the key. The return seems to be correct this time. (But perhaps it would cause an issue if CAR01 and CAR02 might have the same Odometer one day?)

And I have no clue about how to achieve the Goal B and C. Am I performing the key's setting and formula correctly? I am so new to the AppSheet, please give me some suggestions for it. Thank you so much.

[Suvrutt_Gurjar · Accepted solution]

Generally aggregating record wise values is not ideal with AppSheet. Please note and try below.

1) MAXROW() works with only on row key columns. So it will return only key column of the max record depending on max condition. 

2) You may not have odometer as the key. It does not sound like a good choice for key.

3) Instead you could add another column called say [ID] with UNIQUEID() as initial value , keep it hidden and make it a key column.

4) Please add a VC called [Latest Odometer] with an expression something like [Odometer]+ [KM Run] 

5) In the initial value of the [Odometer] column, please add an initial value expression something like 

MAX(SELECT(Table_Name[Latest_Odometer],[CarName]=[_THISROW].[CarName],[Date]=[_THISROW].[Date]))

In the valid_if of the [Odometer] column you could add an expression something like 

[_THIS]>=MAX(SELECT(Table-Name[Latest_Odometer],AND([CarName]=[_THISROW].[CarName],[Date]=[_THISROW].[Date], [_RowNumber]<[_THISROW].[_RowNumber])))

The suggestion step 5 will compute the latest odometer reading from the previous record for the same car and present it as the initial value for the new record being added. The user can then just update the [Km_run] to get the latest odometer readin for that record. The solution assumes , each increasing "Odometer" reading for the same car will be recorded in a new higher row number. Also deletion of intermediate records may cause a computation error.

Please test well.