Second sort variable with First sorted value function

[jgreen95]

I am listing regions by number of failures and occasionally 2 have the same number of failures, is there a way to add a second sort varibale e.g. alphabetical? as i want to show both on my list and currently it is just showing null.

[Digvijay_Singh]

May be try distinct in the first sorted value.

[tresesco]

You can. But that depends on your data. Or, you can use concat() additionally to keep multiple categories like:

=FirstSortedValue(Aggr(Concat(DISTINCT Region, ','), Failure), -Aggr(Failure,Failure))

 

[jgreen95]

My current formula is:

=FirstSortedValue(Authority, -Aggr((COUNT(DISTINCT{<[Inspection Outome]={"Failed (Higher Risk/Dangerous)"},
[Inspection Logged-Notification Created Date.MyCalendar1.WeekEnd] = {">$(=[Current Week])"}>} INSPECTION_NOTICE_ID )), Authority),5)

 How would i add a second sort variable to this?

[jgreen95]

Id prefer not to concat if possible, how would i add an alphabetical sort as the second sort variable?

[tresesco]

Could you share a sample and explain your exact (how and where) expected output?

Note: For second alphabetical sort you could rank(), like:

=FirstSortedValue(Region, -Failure- Aggr(Rank(Region), Failure,Region))

[Mazilinho10]

Hello Tresesco,

I am trying to achieve the same thing on my dataset. the reference column in my case is a calculated field: aggr(count(distinct JourneyID), ArriveToLocation). But when i use the following 

=FirstSortedValue(Aggr(Concat(DISTINCT ArriveToLocation, ','), -aggr(count(distinct JourneyID), ArriveToLocation)), -aggr(count(distinct JourneyID), ArriveToLocation)) the formula is not working as expecte (it's displaying a "-"). Can you please advise me what i am doing wrong? Thanks!