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

    Calculated Field (LOD) as Lookup Criteria




      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.201801VanessaDaniel
      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])}).


      Thanks for your help.