Use maxdate within a year

[QlikNewbie20]

Hi Qlik Experts

I have two tables. One with Customer ID and joining date and another with Customer ID and transaction date.

I want to measure subscription age of customer using the last transaction date of that year when I select year as dimension.

Example: With the tables below, if I select Date/Year = 2020, Then  subscription age for A001 should be (25.10.2020)-(01.05.2019)

CustID	JoiningDate
A001	01.05.2019
A002	02.05.2019
A003	03.05.2019
CustID	Date
A001	23.12.2019
A002	05.05.2019
A003	05.08.2019
A001	30.04.2020
A001	25.10.2020
A002	01.06.2020
A003	09.01.2020
A003	23.06.2020
A003	12.12.2020

I've been trying to use the Firstsorted and Maxdate functions but to no avail.

Any nudge in the the right direction would be greatly appreciated.

Thanks

[GaryGiles · Accepted solution]

With CustID as your dimension, you could use the following as your measure:

aggr(max(Date)-Only(JoiningDate),CustID)

or

aggr(max(Date),CustID)-JoiningDate

[QlikNewbie20]

Brilliant Thanks. Worked like a charm!