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

Get lastest name for customer

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

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

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

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