5 Replies Latest reply: Apr 30, 2016 5:22 AM by Bruno Bertels RSS

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



      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