Field selection using two criteria: one simple and one complex

[MarcosSanz]

I have a table with year, people ID, and a column that says if he/she is retired or not.

People retired is acumulated every year. So at 2020, the table shows people retired during 2018, 2019 and 2020.

I want to show, per year, only the ID of new people retired, excluding people retired during previous years. I attach an excel sheet with the original table, the filter I need an the final answer desired.

Thanks.

[MarcoWedel]

Derive the retired year as separate field in the script and use this year as dimension

[MarcoWedel]

e. g. like

Left Join (tabPeople)
LOAD ID_persona,
Min(Year) as RetiredYear
Resident tabPeople
Where Retired = 'Yes'
Group By ID_persona ;

[NZFei · Accepted solution]

Dim1: Year

Dim2: ID_persona

Expression:

if(Year=min({<Retired={"Yes"}>}total <ID_persona> Year),
'Yes'
)

[MarcosSanz]

Thank you very much. It works!

Now I have to understand better how 🙂