Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
QlikNewbie20
Contributor II
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)

CustIDJoiningDate
A00101.05.2019
A00202.05.2019
A00303.05.2019
  
CustIDDate
A00123.12.2019
A00205.05.2019
A00305.08.2019
A00130.04.2020
A00125.10.2020
A00201.06.2020
A00309.01.2020
A00323.06.2020
A00312.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
GaryGiles
Specialist
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

View solution in original post

2 Replies
GaryGiles
Specialist
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

View solution in original post

QlikNewbie20
Contributor II
Contributor II
Author

Brilliant Thanks. Worked like a charm!