Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Brilliant Thanks. Worked like a charm!