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?

[sunny_talwar]

May be add DISTINCT

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

[RonaldDoes]

Hello Sunny,

Thanks for your suggestion. Unfortunately, this appears to return only a correct result for the first of the "shared first" agents.

Ranked agents

Rank	Agent
1-2	A
1-2	B
3	C
4	D

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

Returns: A (as expected).

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

Returns: C (expected result: B)

[sunny_talwar]

Try this

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

=FirstSortedValue(DISTINCT [Agent], -Aggr(RangeSum(Sum({<SomeDateField =  {">=$(=DATE(MonthStart(TODAY())))"}, [Agent group] = {"A"}>}[#Event Counter]), Rank(Agent)/1E10), [Agent]), 2)

[swuehl · Accepted solution]

Maybe add a distinguishing, tie breaking small amount below 1:

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

=FirstSortedValue(DISTINCT [Agent], -Aggr(Sum({<SomeDateField =  {">=$(=DATE(MonthStart(TODAY())))"}, [Agent group] = {"A"}>}[#Event Counter])+1/FieldIndex('Agent',Agent) ,[Agent]),2)

[stigchel]

If you want ot randomly pick one, maybe you can use the rand function to adjust the sort weight. Something like

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

[RonaldDoes]

Genious! Working perfectly. Thank you very much, Stefan.

I've taken the liberty of creating a little demo of my original solution, Sunny's suggestion (just adding DISTINCT) and your FieldIndex-based solution which I've attached.

As for the Rand()-suggestion, that would work, however I have multiple objects showing details of the "picked" agents, that would also display randomly display the details for one of both, while the caption could display the other. Thanks for the suggestion, though.

[sunny_talwar]

Did you miss my next suggestion my friend?

It seems to be working just like Stefan's (in your case at least)....

[sunny_talwar]

Oh and I don't even get a helpful response like others . I guess I was not really helpful at all

[swuehl]

Yes, Sunny's solution should work basically the same way.

It's up to you, Ronald, to define the criterium that you can use for the tie breaking.

Note also that my solution has a little flaw:

I was telling to add a small amount below 1 (assuming you are summing up integer values), but then I've used

+1/FieldIndex('Agent',Agent)

which actually equals 1 for the first agent in load order so might corrupt your ranking.

+1/(FieldIndex('Agent',Agent)+1)

should correct for this.

But as I said, there are other criteria possible (like the number of orders one needs to get the total amount, the smaller the better, or something similar, or just the alphabetical order like Sunny used (well I don't like that idea from a personal view) )