4 Replies Latest reply on Jun 19, 2018 4:22 PM by Deepak Rai

# Calculated Field (LOD) as Lookup Criteria

Hi,

Version 10.4.6.

I need help with a calculated field, please.

My data is currently as shown in the 3 columns below.  We have three customers -- Acme Products that has been with us since Jan 2018, XYZ Industrial that has been with us since Mar 2018, and Dropped Corp that ceased being a customer in Feb 2018.  Each customer has been managed by different account managers during the time that they've been with us.  (Acme started with Cindy, then moved to Jeff and is now with Mark.  XYZ started with Aaron, then moved to Cindy.  Dropped Corp started with Vanessa then moved to Daniel.)

Customer Name
Year MonthAccount Manager
Acme Products201801Cindy
Acme Products201802Cindy
Acme Products201803Jeff
Acme Products201804Jeff
Acme Products201805Mark
XYZ Industrial201803Aaron
XYZ Industrial201804Aaron
XYZ Industrial201805Cindy
Dropped Corp.201801Vanessa
Dropped Corp.201802Daniel

I'd like to come up with a calculated column that shows me the current account manager in all rows for that customer.  Meaning that since Mark is the account manager associated with the largest [Year month] for Acme, his name appears in all rows for Acme.  Likewise with Cindy and XYZ and Daniel with Dropped Corp.

Customer Name
Year MonthAccount ManagerCurrent Account Manager (Calculated Field)
Acme Products201801CindyMark
Acme Products201802CindyMark
Acme Products201803JeffMark
Acme Products201804JeffMark
Acme Products201805MarkMark
XYZ Industrial201803AaronCindy
XYZ Industrial201804AaronCindy
XYZ Industrial201805CindyCindy
Dropped Corp.201802DanielDaniel

The following LOD expression got me to identify the maximum [Year Month] for each Customer:

{FIXED [Customer Name] : Max([Year Month])}

but I'm lost on how to translate this to the account manager name.  I'd need something like LOOKUP("Account Manager" where "Customer Name" = [Customer Name] and "Year Month" = {FIXED [Customer Name] : Max([Year Month])}).

• ###### 1. Re: Calculated Field (LOD) as Lookup Criteria

Thanks

Deepak

1 of 1 people found this helpful
• ###### 2. Re: Calculated Field (LOD) as Lookup Criteria

Hi Ryan

Sorry Forgot To attach...Here is attachment.

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close thread

• ###### 3. Re: Calculated Field (LOD) as Lookup Criteria

Thanks!  It took me 3 days of googling to NOT get the right answer.  You answered in a few minutes.  Amazing!

• ###### 4. Re: Calculated Field (LOD) as Lookup Criteria