FirstSortedValue for the two same values

[Ethel]

Hello,

This is my formula for the age for employees. I need to to put the biggest age interval in the title. 

FirstSortedValue(Age,-aggr(max(aggr(avg(aggr(sum([HeadCount]),[Period], Age)), Age)), Age))

My problem is that for particular moment I have two ages(55-60 and 60-65 ) that have exactly the same number of people(33 people). Probably because of that my formula doesn't show anything. For all other cases it works fine. How should I improve it and say to show youngest age for example if there are two groups with the same number of people?

Thanks a lot in advance!

[Or]

Have a look at:

https://community.qlik.com/t5/QlikView-App-Dev/Firstsortedvalue-giving-NULL-when-value-is-tie/td-p/6...

https://community.qlik.com/t5/New-to-Qlik-Sense/Concatenate-Results-if-FirstSortedValue-is-a-Tie/m-p...

For approaches to deal with the "Ties return null" issue for FirstSortedValue()

[rwunderlich · Accepted solution]

Two options I know of:

1. Add the DISTINCT keyword to FirstSortedValue and let Qlik choose. I think it's the first one in load order.

2. Add some small bias to your FirstSortedValue parameter 2. For example, -(Age * .001)

I realize Age is a string you may have to do something like -(SubField(Age,'-',1) * .001)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Ethel]

thank you!

Could you please show in the formula how it should be?

I tried both distinct and (SubField(AgeBand,'-',1) * .001) and it is not working.

Maybe I'm not using it correctly?

Thanks a lot in advance!

[Ethel]

Thank you!! ! It worked! I had to add distinct to the age(first parameter).