3 Replies Latest reply on Oct 14, 2016 12:51 PM by Ivan Young

    Get lastest name for customer

    Andy Tran

      Hi, below is a sample of my data:

       

      Customer IDNameDate

      123

      Andy

      2015/10/13

      123Andy2015/11/13
      123Mark2015/12/13
      456Jenny2015/12/13
      789John2015/10/13
      789Smith2015/11/13

       

      Given a customer ID, I'd like to be able to retrieve the latest name for the customer, my desired output is as follows:

       

      Customer IDNameDate
      Latest Name

      123

      Andy

      2015/10/13

      Mark
      123Andy2015/11/13Mark
      123Mark2015/12/13Mark
      456Jenny2015/12/13Jenny
      789John2015/10/13
      Smith
      789Smith2015/11/13
      Smith

       

      I am able to get the latest date for each customer using a LOD calculation: { FIXED [Customer ID]:MAX([Date])}, But I wasn't able to use it to do a self-join on the data source or somehow use the result in a LOOKUP.

       

       

      Any assistance is much appreciated,

      Thanks

        • 1. Re: Get lastest name for customer
          Ivan Young

          IF [Date] = {FIXED [Customer ID] : Max([Date]) } THEN [Name] End

           

          Since this is an LOD you will need to add your Date filter to Context so that it will act on the LOD.

          • 2. Re: Get lastest name for customer
            Andy Tran

            Sorry, I realized my question may not have been clear.. I have edited the question.

             

            I have tried using your formula, but the result would be NULL for dates that are not equal to MAX([DATE]), The reason I want it this way is because I want to show the customer's most recent name in my report while I aggregate the data based on Customer ID

            • 3. Re: Get lastest name for customer
              Ivan Young

              If you need it in all rows it's

              {FIXED [Customer ID] : MAX(IF [Date] = {FIXED [Customer ID] : Max([Date]) } THEN [Name] End) }

               

              Min will also work so you could do {FIXED [Customer ID] : MIN(IF [Date] = {FIXED [Customer ID] : Max([Date]) } THEN [Name] End) }

               

              Let me know if you need any explanation of what is going on with the expression.

              1 of 1 people found this helpful