Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be try distinct in the first sorted value.
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))
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?
Id prefer not to concat if possible, how would i add an alphabetical sort as the second sort variable?
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))
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!