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?

    Pia Rieppo

      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