Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
Have you tried with DISTINCT may be
=If(HistTab.Result_Code <> Null(), FirstSortedValue(DISTINCT HistTab.Short_Reference, -HistTab.RefDate))
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
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
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
Would you be able to share a sample?
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]])
Try
aggr(Maxstring(HistTab.RefDate),HistTab.Company)
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.