Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Not applicable

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

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
1A1/1/20141/1/2016
1A1/1/20151/1/2016
1B3/1/20151/1/2016
1C2/10/20152/1/2016
2A2/23/20152/1/2016
1 Solution

Accepted Solutions
brunobertels
Valued Contributor II

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

Hi

MAy be something like this ( front end) :

mesure:

num(DealCloseDate

-

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

hope it helps

5 Replies

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

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

Not applicable

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

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
Valued Contributor II

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

Hi

MAy be something like this ( front end) :

mesure:

num(DealCloseDate

-

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

hope it helps

Not applicable

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

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
Valued Contributor II

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

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