# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for
Did you mean:
Contributor II

## Use maxdate within a year

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

1 Solution

Accepted Solutions
Specialist

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

2 Replies
Specialist

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

Contributor II
Author

Brilliant Thanks. Worked like a charm!