FirstSortedValue and AGGR

[#1]

Hi, I am trying to find the last time our reps made contact with the customer and what they entered in the "REF" field. The relevant fields in my table are Sales_Rep, Short_Reference, and RefDate (MM/DD/YY hh:mm). When I put in the RESULT CODE as a dimension, I get the following:

However, I only want to the see the very last entry, not last by ResultCode. But when I take out the ResultCode dimension, I get:

The two RefDates are clearly not the same, so I can't see why FSV can't differentiate between them (which I assume is the reason for the "Null" return). I tried:

=If(HistTab.Result_Code <> Null(), aggr(FirstSortedValue(HistTab.Short_Reference,-HistTab.RefDate),HistTab.Company))

and

=If(HistTab.Result_Code <> Null(),FirstSortedValue( aggr(HistTab.Short_Reference,HistTab.Company),-HistTab.RefDate))

all with the same "null" result.

I'm sure I'm missing something obvious; can anyone help me out? thanks, Kevin

[#2]

I must not be expressing the problem very well.

There are TWO values for reference. I don't want the one that is alphabetically first or last (as MaxString would give me). I want the one with the LATEST REFERENCE DATE, which is clearly shown in the table above. The REF dates are unique, the REF strings are unique. In the screenshot above, I ONLY want to see the REF string for the most recent REF date (5/28/14). I thought FirstSortedValue(expr, sortweight) would do exactly that, and in fact it does in most applications. I can't understand why it's not working here.

[Gabriel]

Hi,

Ok that's fine. Thinking of it the sort weight is supposed to return Numeric value. So in that case why not rank the HistTab.RefDate in the script and just return the rank value in your FirstSortedValue (Sort Weight). Just have a small load of your ID (whatever that identify each row) and HistTab.RefDate with Order By HistTab.RefDate Desc or Asc then rename the field (left join it to your FACT table) the use the new field in your FirstSortedValue (Sort Weight).

[#4]

RefDate is already Numeric; I just use "Date(RefDate,'MM/DD/YY hh:mm) to display it in readable form, as opposed to:

Don't see why I should need to add RANK to get a numeric result; it already is numeric.