Calculating a value based on a previous row containing same user_id?


#1

Hi all,

This is a little hard to explain:
I have a table that contains a link to a user table (user_id), and a set of values… we’ll just call this “points.”

What I would like to do is populate another field, “points_since_last”, using “points” from that row, and “points” from the previous row with that same user_id. So the formula will be [“points” from this row] - [“points” from the previous row that has the same user_id or 0 if none exists]

Row:
ID | user_id | date | points_since_last | points


#2

I’m not sure that this can be done… mainly because there’s no way to look up a “previous” record (as far as I know); you can only look up “first” and “last”, by way of using the record creation timestamp and the MIN/MAX lookup functions.

EDIT: There is a way, but it’s a bit of a hack – you have to make a link to the previous record: