8 Replies Latest reply: Feb 28, 2011 4:40 AM by Andy McCallum RSS

    Issue with showing a record with the latest date in a chart

      I am trying to get the location of a Serial numbered stock item (SERIALNUMBER) in a table chart by looking for the record which has a status of "I" (STATUS)and looking for the latest date

       

      For example I have 2 records - the 1st with Location A on 1/8/2009 and the 2nd record with Location B on 30/4/2010.

       

      The result I would like to see is Location B with a date of 30/4/2010

       

      If I use AGGR & MAX functions as shown below, I get the correct date in the expression but the 1st record in the Dimension showing Location A

       

      aggr(max({$<STATUS = {'I'}>} DATE), SERIALNUMBER)

       

       

      If I use the FirstSortedValue function with a - Date, I get both records showing with the serial number in the expression

       

      FirstSortedValue ({$<STATUS = {'I'}>} SERIALNUMBER, - DATE)

       

      Any suggestions anyone?