Conditional LookUp

[CJG]

I have a table of different meter readings for a number of different machines listed by date.

I wish to calculate the difference between certain meter readings since the last reading - today's meter reading minus the last meter reading for this machine. I have tried (based on search of other posts)

LOOKUP (MAX (SELECT (Meter Readings[_ROWNUMBER],([_THISROW].[Serial Number] = [Serial Number]))), "Meter Readings", "_RowNumber", "Total Plays")

But this calculates the difference as "0" as today's reading is now the MAX. 

Help? P.S I am new to AppSheet but digging it!

[Kirk_Masden · Accepted solution]

I see.  How about this?

[Total plays] - MAX(SELECT(Meter Readings[Total plays], (AND([Serial Number] = [_THISROW].[Serial Number],
[Total plays] < [_THISROW].[Total plays]),FALSE))

[Kirk_Masden]

Let's start by checking the basics.  What is the table name and what is the column name? 

Here's the select format:

SELECT(from-dataset-column, select-row?, [distinct-only?])

https://support.google.com/appsheet/answer/10108207?hl=en

Let's say your table name is "Data" and the column name is "Meter Readings."  In that case, I think something like the following might work:

[Meter readings] - MAX(SELECT(Data[Meter Readings], ([Meter Readings] < [_THISROW].[Meter Readings]),FALSE))

P.S. This assumes that readings are always going up.  If readings can go up and down, then a different formula would be required.

[CJG]

Table Name = "Meter Readings", Column Name = "Total Plays"

Thanks for the response but doesn't solve the problem as the table has many machines in it - each with a distinct Serial Number. I am wanting to calculate the difference between today's meter reading and the previous meter reading for "Total Plays" for the particular machine (where the Serial Number is matching on the rows).

The meter readings only go up. 

[Kirk_Masden · Accepted solution]

I see.  How about this?

[Total plays] - MAX(SELECT(Meter Readings[Total plays], (AND([Serial Number] = [_THISROW].[Serial Number],
[Total plays] < [_THISROW].[Total plays]),FALSE))

[Kirk_Masden]

By the way, the way I'm recommending may work (I hope) but using SELECT() expressions in this way is "expensive" (not efficient; may cause problems).  There may be a better approach given your situation.  One idea is to put this kind of expression in an action and then write the difference with the previous reading directly into the spreadsheet.  This would increase writing but greatly reduce the amount of calculating and recalculating that SELECT() expressions cause.

[CJG]

Thanks Kirk, works nicely - Legend!

It is even more efficient as I was bringing through the previous reading into a column then I had another column with the actual difference calculation in. This was across about 10 different meters so is 10x more efficient. 

[CJG]

So I have run into a problem with this - the meter readings can be zeroed.

When this is the case the above formula is calculating the difference between the current meter reading and one of the prior meter readings that are for this Serial Number and "< [_THISROW]". Not necessarily the latest previous meter reading.

I need to be calculating the difference in the meter reading between this reading and the latest previous reading for this "Serial Number".

Is anyone able to advise the additional condition required to enable this please.

[CJG · Accepted solution]

FYI - Solved this by adding a field to record how many "days past" since last reading as there will always be a date on any reading including when zeroed.

Then changed the formula so that the meter readings match the Serial Number and where the Date of the previous reading plus this reading's Days Past equals this reading's Date as follows:

[Total Plays] - MAX(SELECT(Meter Readings[Total Plays], (AND([Serial Number] = [_THISROW].[Serial Number], [Date] + [_THISROW].[Days Past] = [_THISROW].[Date])), FALSE))

Not sure if this is the most efficient or correct way of solving the issue but it appears to work. Any advice on improvement is welcome.