Distance Between This Row and The Previous Row

[Ryan_Wagner]

Hi all, 

I looked around the community and couldn't figure out how to solve this one, though it is probably very simple. 

I'm trying to calculate the distance between the current row (lat,long column) and the previous row (in the same lat,long column.)

I tried using _THISROW_BEFORE and _THISROW, etc., but I could not make it work.

The result will be used in another column. I can provide more info, but I am assuming this is an easy one (just not for me.)

[Ryan_Wagner]

This is what I tried last. (The "Start Waypoint" column is the "lat,long" I'm referring to. The multiplication at the end is to get the distance in feet.)

DISTANCE([_THISROW_AFTER].[Start Waypoint],([_THISROW_BEFORE].[Start Waypoint]))*3280.84

[TeeSee1]

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

[_THISROW_BEFORE] gets a value within a single row and not across different rows.

What you need is to get a value from a different row using a SELECT or LOOKUP by identifying the "previous" row.

How you identify your "previous" row depends on how you have created and use your app. If this is a multi-user app, then it becomes tricky. If the rows are entered in an orderly manner then [_RowNumber] can be used.

[Ryan_Wagner]

Great answer, thank you. 

I forgot that "Row Number" could be used. It will be for a single-user, so I will give that a try and let you know how it goes.

Thanks again.

[SkrOYC]

For your reference:

SELECT() - AppSheet Help
MAX() - AppSheet Help
ORDERBY() - AppSheet Help
INDEX() - AppSheet Help
TOP() - AppSheet Help

Remember there are always different ways to do the same thing inside AppSheet

[Ryan_Wagner]

Well, I have made a royal mess of this attempt at an expression. 

Normally, I wouldn't copy and paste my attempt here, but I will anyway:

DISTANCE([_THISROW].[Start Waypoint],[_RowNumber].[Start Waypoint]-1)*3280.84

I also tried: 

DISTANCE([_THISROW].[Start Waypoint],[_THISROW].[Start Waypoint]-1)*3280.84

Hopefully you can see what I was trying to do. To reiterate. I want to get the distance from one row to the previous row (both in the "waypoint" column.

[SkrOYC]

I don't see any SELECT/LOOKUP function being used

[Ryan_Wagner]

I struggle with those two functions, though I've seen great examples of what they can do here on the forum. If you are up for helping me construct my expression with one of those, it would be a tremendous help. 

[Steve · Accepted solution]

Try this:

DISTANCE(
  [Start Waypoint],
  LOOKUP(
    MAX(
      SELECT(
        table[_ROWNUMBER],
        ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])
      )
    ),
    "table",
    "_ROWNUMBER",
    "Start Waypoint"
  )
) * 3280.84

Replace the two occurences of table with the name of the table.

[Ryan_Wagner]

Worked beautifully!

Thank you for that! This will be a huge timesaver for me.