Unique rank

[RonaldDoes]

Hi Qlikies,

I hope one of you can help me out.

What are you trying to achieve?
We'd like to show some details for the top 3 agents based on an event counter. To this end, I've ranked them and use FirstSortedValue to pick up the field "Agent" for, let's say, the top agent:

=FirstSortedValue([Agent], -Aggr(Sum({<SomeDateField =  {">=$(=DATE(MonthStart(TODAY())))"}, [Agent group] = {"A"}>}[#Event Counter]),[Agent]))

And for the second best agent:

=FirstSortedValue([Agent], -Aggr(Sum({<SomeDateField =  {">=$(=DATE(MonthStart(TODAY())))"}, [Agent group] = {"A"}>}[#Event Counter]),[Agent]))

Fair enough, so what's your problem?

This all works wondefully well, and I was really pleased with myself, right until the moment that the first two agents had the same Sum([#Event Counter]).
Then, the Rank() returned "1-2" and there can be no FirstSortedValue, apparently. The above expression just returns Null (and my dashboard becomes an incomprehensible mess).

What would you like?

I'd like to (randomly) pick just either one of the agents to show the values for. Is there a way to have Rank() return a unique rank number?

[RonaldDoes]

Hello Sunny,

You're completely right.

I made a mess of it when I tried it out in my actual application and it threw a "Rank function is not allowed inside aggregation".

When implemented correctly it does of course work just as well as Stefan's solution.

Thank you for your help, it's most appreciated.

With kind regards,

Ronald

[RonaldDoes]

Hi Stefan,

Thank you for the further improvement.

My "specification" for the tie-breaking was "completely random", so I guess the best solution here, from an academic viewpoint, would be the one that offers the best performance.

I have absolutly no clue as to which of the two that might be, so I'll gladly leave that up to someone far smarter than me to decide.

For all practical intents and purposes (future reference), I've marked your solution as correct.

[effinty2112]

Hi Ronald,

I know I'm a latecomer to this. I dislike the FirstSortedValue function because of the way it handles ties.

=Concat(Aggr(if(Rank(

Sum({<SomeDateField ={">=$(=DATE(MonthStart(TODAY())))"}, [Agent group] = {"A"}>}[#Event Counter])

),1,1) =1,[Agent]),[Agent]),',')

Will return a concatenation of Agents tying for first place rather than being null. Change the figure in red to return the ranking you're looking for.

Cheers

Andrew

[RonaldDoes]

You were really helpful. I mistakenly marked my own reply to your original suggestion as helpful instead of your suggestion. I've corrected this.

Please accept my humble apologies.