13 Replies Latest reply: Dec 28, 2016 12:28 PM by Sunny Talwar RSS

    FirstSortedValue

    Adotey Kwame

      Hello All,

      My requirement is to get only the records highlighted in yellow with the bold font as in the image attached.

       

      Essentially if a record is amended the its ID get updated i.e ; followed by the number of times.So in such instances I only want the most recent update. thus the expression ;

      =FIRSTSORTEDVALUE(AGGR(SUM(DISTINCT AMOUNT),CLIENT,ID)
      ,
      -
      AGGR(SUM([AMENDMENT NUMBER]),CLIENT,ID)) where Amendment number is derived as MID(ID,14,3)) AS [AMENDMENT NUMBER].

      But unfortunately this fails to work for client's with no amendment like Moses, Victor just a few.

       

      Please help, Thanks

      image.PNG