FirstSortedValue Returns Null

[#1]

I have looked at several threads about FirstSortedValue function and have followed the steps suggested in various scenarios, but for some reason I'm not always getting a value returned in my FirstSortedValue expression.  I have tried this both in the script and in the chart expression but nothing is resolving the issue. 

The scenario is this:  For each policy I have a field called StatusLabel that describes the status of the policy, and a SortID field that identifies how i want to sort the statuses.  For each customer, I want to return the FirstSortedValue of the StatusLabel sorted by SortID in reverse order (the highest numbered SortID).  My expression is:

FirstSortedValue(StatusLabel, -SortID)

Sometimes I get a value and sometimes I don't, and I can't figure out what the difference is.  I have attached a sample document that is filtered to a customer that clearly has several policies of varying statuses associated, but the FirstSortedValue function is not returning a value either in the script by returning a value in the PersonStatus field, or in the chart expression where I tested it.

Can anyone see what I'm missing?

Thanks

[swuehl · Accepted solution]

Try

=FirstSortedValue(DISTINCT StatusLabel, -SortID)

[sunny_talwar]

You are using a field which isn't even available in your database -> Rank. What is your expected Status from these and what is the logic?

[#4]

Sorry, i changed the name for the field Rank to 'SortID' in the script thinking maybe the issue was that I used a QV keyword as a field name.  I forgot to update that expression to use the revised field name before i saved and uploaded the sample doc.

[#5]

Interesting... that works like a charm, although QV doesn't seem to want to recognize that DISTINCT is valid for use in that expression.  Thanks, I knew it was probably something that simple!

[Gabriel]

Hi,

First, the Rank field is  been renamed in the script to SortID. So in object CH03 you have to change the -Rank to -SortID. Now it should read FirstSortedValue(StatusLabel, -SortID)

Secondly, some customer ID is frequency count is more than 1. So whenever Customer ID frequency count is more than 1 FirstSortedValue will return Null value '(-)'

May be use this

FirstSortedValue(DISTINCT StatusLabel, -SortID)