Duration for max date from one column for a specific other column value?

[#1]

Hi,

I'm fairly new QlikSense user and I'm having trouble coming up with an elegant solution to the following.  Simplified: I want to calculate the duration between two events: the latest activity for a Customer ID#1 and the deal close date.  The trouble is there are many separate activities for the customer so I need to calculate the duration only for the latest activity and the deal close date i.e. something like  DealCloseDate-(max(ActivityDate)) for a given customer ID.  The added complication is that there are different types of activities and I only want to calculate the duration for when activity=A.  So in this instance for customer #1 the correct answer would be 365 days i.e. duration between 1/1/2015 and 1/1/2016.

I managed to create another table using resident table idea to isolate only activity dates fro activity A  ( resident from where activity = A) but then got kind of stuck.

Any suggestions?
Thanks in advance!

Customer ID	Activity Type	ActivityDate	DealCloseDate
1	A	1/1/2014	1/1/2016
1	A	1/1/2015	1/1/2016
1	B	3/1/2015	1/1/2016
1	C	2/10/2015	2/1/2016
2	A	2/23/2015	2/1/2016

[sunny_talwar]

Is DateCloseDate a field coming from the database? Are you looking for a solution in the script or front end?

[#3]

Sunny,

Thanks for your consideration of this issue.

Yes, it is all coming from a database in a single table.   I'm open to solution in load script or front end or combination of both. 

[brunobertels · Accepted solution]

Hi

MAy be something like this ( front end) :

mesure:

num(DealCloseDate

-

aggr(date(Max({$<[Activity Type]={'A'}>}ActivityDate)),[Customer ID]))

hope it helps

[#5]

This community is awesome!  Thank you very much Bruno!!!  Merci beaucoup!

This saved me many hours of frustration trying to figure this out.

I was not familiar with aggr but this works exactly as needed.  I ended up doing the following:

round(date(DealCloseDate)-aggr(date(Max({$<Activity={'A'}>}ActivityDate)),CustomerID))

[brunobertels]

Hi

I'am glad that it helped you

see this links for more details about aggr function :

https://community.qlik.com/videos/4140

https://community.qlik.com/blogs/qlikviewdesignblog/2015/10/05/pitfalls-of-the-aggr-function

bruno