Aggregate conditions in set analysis

[Kavara]

Please see snippet of table attached below:

There is a "Based Year" for every gift year. I need to create a filter for choosing a 'Based Year.' Once a 'Based Year' is selected, I want to show revenue data greater than 10,000 for the most recent five consecutive years,exlcuding the 'Based Year.' If any year within those five is missing, it shouldn't be displayed at all.

For example if 2022 is selected, only Gift Family Donor ID Key with revenue greater than 10,000 for years 2021, 2020, 2019, 2018 and 2017 should be displayed and if 2010 is selected only Gift Family Donor ID Key with revenue greater than 10,000 for years 2009, 2008, 2007, 2006 and 2005 should be displayed. 

Please note, I don't not want to hardcore 5 years or 10,000 Revenue in load editor, as it is required to be a variable in the event it needs to be changed by the User.

For the snippet shown above, if Based Year 2002 is selected then ID "MS1000022" would not be displayed as it only has 3 consecutive years below the selected Based year and not 5 as required and "MS1000015" would not be displayed as it doesn't meet the 10,000 revenue requirement.

Please provide advice on how to achieve this. Thank you all!

[marcus_sommer · Accepted solution]

Maybe something like this:

aggr(
   count({<
     GiftYear = {">=$(max(BaseYear)-5)<$(max(BaseYear)"},
     Revenue = {">10000"}>} ID) >=5,
ID)