i have a field that i'm using a date selection on to choose which row of data to show currently. it works as it should. However, if i want to choose the row above it for another formula how would i do that?

for example here is what my field looks like


rate  date
1        1/01/2001
2        1/01/2003
3        2/05/2003
4        5/15/2010
5        5/14/2013

i can write a simple formula to show me the most recent one (if ({date}) > some date then {rate}) which is what i have but what would i do to see the row above it?


thank you in advance

I'm not sure that will work or how to do it actually. what my client is looking for is if an employee got a raise which would be indicated by the active date beside the rate then they want to see the difference it makes in yearly salary.

i can get the employee salary by taking the active rate * default hours * pay frequency.

the only thing i'm not sure of is how to make a similar formula to say
rate before the active rate * default hours * pay frequency.

if your data is ordered by date, and you want the rate from the date before the current line, there is the PREVIOUS() function which will look back 1 row in the dataset.

There is 1 caveat that I am aware of...namely, it doesn't care about groupings, so extra caution is needed for that case.

Hopefully this is what you are looking for.