Making calculations based on data from n previous years in a table where each row identifies a year

Valnar — Fri, 07 Jul 2023 13:09:20 GMT

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.

Re: Making calculations based on data from n previous years in a table where each row identifies a year

Valnar — Fri, 07 Jul 2023 14:16:18 GMT

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/ta-p/1483033

topic Making calculations based on data from n previous years in a table where each row identifies a year in App Development

Making calculations based on data from n previous years in a table where each row identifies a year

Re: Making calculations based on data from n previous years in a table where each row identifies a year