Rolling average for 12 months ignoring date selection

[QlikSenseQuestion]

Hey everyone,

I'm new to Qlik Sense and probably have a very stupid question, but I have been struggling for a couple days now.

I am currently trying to get a rolling 12-month average to show how much revenue is made relative to the target. The chart I'm using is a table that only shows 1 month per row.

The user wants to select a year and get the rolling average per month of the prior 12 months. For example these are the true target percentages per month for 2020 and 2021.

However when I select 2021 and use my formula it only gives me the rolling average of the selected year. 

As you can see for Jan 21 it just takes the value of 71.08% and takes an average of that. For feb 21 it takes the value of Jan and Feb and takes the average of those 2.

My goal is to take 2020 in account despite my selections and have an average of 74.78% (average of Feb 20 - Jan 21) instead of 71.08% (average of only Jan 21).

The formula I'm using is as follows:

rangeavg(above(aggr((sum([Actual profit])) / (sum([Target])), [YearMonth]), 0, 12))

[rubenmarin]

Hi, with an epxression it will beghin from 0, not using the date before the first month shown on chart, you can have precalculated accumulated in script and use this values, or follow a solution like the AsOf table: 

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

[QlikSenseQuestion]

Thanks for the response! I've been trying to use the AsOf-table, but it's not working for me. I run into too many errors when I try applying it to my datamodel. Isn't there a way to ignore date selections in the rangeavg chart function? I've tried: 

rangeavg({<[Year-Month]=>}above(aggr((sum([Actual profit])) / (sum([Target])), [YearMonth]), 0, 12))

or

rangeavg(above(aggr((sum({<[Year-Month]=>}[Actual profit])) / (sum({<[Year-Month]=>}[Target])), [YearMonth]), 0, 12))

However, both don't seem to work. I had expected the calculation to just ignore date selections by the user.

I'm probably very far off the actual solution, because I'm still an absolute beginner at this.