How to calculate 5-year average despite of year filter selections?

[Umeiko]

Hi everybody

I met a Qlik Sense KPI calculatioin problem like below：

SUM(if(rtrim([data_yrmon])> SixtyMonths_StartYearMonth AND rtrim([data_yrmon])<= MaxBookingMonth AND [Profit_Item]='Booking' and [data_type]='bbb', [curmo_performance_amt]))/5

which indicates I wanna calculate 5-year average booking amount.

Since 5-year average defines clearly 60-months booking amount average,

so I define 2 variables.

(1) SixtyMonths_StartYearMonth =>find out 60 months ago. For example, if my dataset Largest data year month is 2023/12, it would be 201812

(2) MaxBookingMonth ==>find out the largest/latest booking Year/m. In my example, it would be 202312

My question is, when I use Year/Quarter/Month Filter and selected "2023", the calculation only returns year 2023 booking amount /5 , apparently it is not what I expected.

I know maybe {1} can solve the prlblem to ignore filters, however, my page still places a lot of filters about organizations tree. so {1} is not a good solution.

Is there any other coreect way to mofity my calculations to get my expected result ?

Thanks everybody who replys. Thank you for helping me.

[anat · Accepted solution]

You can bypass year selection in set analysis

[Umeiko]

True,  I think year selection limited the dataset, so that we can not get precisely 60 months average  as year-month filters selected.  Now I decided to answer users: If you want to get 5-year(=last 60 months) average, DO NOT USE YEAR FILTER!!