topic Re: Unique rank in QlikView

Unique rank

RonaldDoes — Fri, 20 Oct 2017 13:02:29 GMT

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?

Re: Unique rank

sunny_talwar — Fri, 20 Oct 2017 13:04:55 GMT

May be add DISTINCT

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

Re: Unique rank

RonaldDoes — Fri, 20 Oct 2017 13:16:09 GMT

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)

Re: Unique rank

sunny_talwar — Fri, 20 Oct 2017 13:19:51 GMT

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)

Re: Unique rank

swuehl — Fri, 20 Oct 2017 13:20:01 GMT

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)

Re: Unique rank

stigchel — Fri, 20 Oct 2017 13:24:25 GMT

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]))

Re: Unique rank

RonaldDoes — Fri, 20 Oct 2017 13:59:20 GMT

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.

Re: Unique rank

sunny_talwar — Fri, 20 Oct 2017 14:03:09 GMT

Did you miss my next suggestion my friend?

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

Re: Unique rank

sunny_talwar — Fri, 20 Oct 2017 14:05:19 GMT

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

Re: Unique rank

swuehl — Fri, 20 Oct 2017 14:13:24 GMT

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) )

Re: Unique rank

RonaldDoes — Fri, 20 Oct 2017 14:14:38 GMT

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

Re: Unique rank

RonaldDoes — Fri, 20 Oct 2017 14:21:50 GMT

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.

Re: Unique rank

effinty2112 — Fri, 20 Oct 2017 14:22:03 GMT

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

Re: Unique rank

RonaldDoes — Fri, 20 Oct 2017 14:23:17 GMT

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.