Select value from future transaction
I have a module with a number of transactions for different entities. For all of these entities, I have a Unique ID (UID) and a valid from date. I also have a line item that uses the RANK function to rank the transactions. See picture below.
Now to the issue; I need a value in the Valid To line item. This value should be the same as the Valid From for the next transaction. For example, The Valid To for the row #5721 should be 3/27/2019.
My initial idea was to do something like this: Valid To = Valid From [LOOKUP: UID, LOOKUP: Rank order+1] but I didn't manage to get that to work.
Does anyone know how to tackle this issue? Any help is appreciated!
Thanks a lot!