Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Contributor II

## 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

``{\$<Year={">=\$(Year-1) <=\$(Year-1)"}>}``

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.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Contributor II
Author

I managed to solve my problem applying the AsOf solution from this article: https://community.qlik.com/t5/Member-Articles/Calculating-rolling-n-period-totals-averages-or-other/...