Making calculations based on data from n previous years in a table where each row identifies a year
I am building a table where I have four columns: Year; allocated resources; total grants; ratio of grants to resources. It is like this:
Now, i need to add a fifth column, which will be a benchmark where I must calculate the average value of the grants to resources ratio for the three previous years. Is this possible? And if so, how can I accomplish this?
I am trying to do that using set analysis, but currently I can't even figure out how to get the value from just the previous year. If I try something like
I only get in each row data from that same year. If I try to apply the MAX function to the year field, I obviusly only get data for the Year thus selected. If I just check for equality, instead of checking for both greater than and smaller than, I get 0.
I am completely stuck and can't figure out how to solve this problem.