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

[Gabriel]

Hi,

May be try to include Max into your expression as below

=If(HistTab.Result_Code <> Null(),

     FirstSortedValue(

          Aggr(

          Max(HistTab.Short_Reference,HistTab.Company)),-HistTab.RefDate))

[sunny_talwar]

Have you tried with DISTINCT may be

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

[#4]

Hi Kevin, try with this:

1. Use the same steps that you used in the chart number one.

2. Hide the field: HistTab.Company

3. Order the table by Last Ref Date

4. Show just one value

You cannot take out the dimension Result_Code because this field use the function firstsortedvalue to order the values.

I hope that it help you

[#5]

Hi Gabriel,

Short_Reference is a string. Max(Short_Reference) won't work, and MaxString would give me, for example, the Ref that starts with "ZZ", which is not what I want. I want the REF with the latest date attached.

thanks, Kevin

[#6]

But the field Short_Reference (SR) is already unique, as you can see from the first screen shot. So I don't see how adding DISTINCT will help.  (And I tried it, and result was still Null).

thanks, Kevin

[sunny_talwar]

Would you be able to share a sample?

[Gabriel]

Hi,

Why not take advantage of the Sort Weigh of FirstSortedValue to rank what to return

firstsortedvalue([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])

[sivarajs]

Try

aggr(Maxstring(HistTab.RefDate),HistTab.Company)

[#10]

Er, that is EXACTLY what the "-HistTab.RefDate" is supposed to do. It weights the sort by date, and the "-" sign indicates to return the last value (as opposed to first) by that criterion.