Determining Rank() within Set analysis

[#1]

Hi,

I am struggling to form a set expression to determine: Who won most of the games?

Here is the Dataset:

load * Inline [

Category, Team, Winner, Points

A, Golden Eagle, Yes, 701

A, Pink Panthers, No, 512

A, Somers City, No, 501

A, Warriors XI, No, 432

B, Somers City, Yes, 654

B, Golden Eagle, No, 610

B, Warriors XI, No, 602

B, Somers City, No, 589

C, Golden Eagles, Yes, 332

C, Warriors XI, No, 312

C, Somers City, No, 290

C, Pink Panthers, No, 228

D, Somers City, Yes, 451

D, Pink Panthers, No, 432

D, Warriors XI, No, 412

D, Golden Eagles, No, 409

E, Golden Eagles, Yes, 660

E, Pink Panthers, No, 630

E, Somers City, No, 601

E, Warriors XI, No, 523

];

If Winner = 'Yes', the team wins in that category. I need to find which team won most of the games (irrespective of category). For example, from my dataset, one can deduce "Golden Eagle" won 3 games. I am unable to form an expression for it.

I need to show who won most of the matches in Caption of text box and number of wins in text box expression. I tried below but it did not help:

aggr(rank(count({<Winner={"Yes"}>}Team),4,2),Team)

[sunny_talwar]

Try this:

=FirstSortedValue(Team, -Aggr(Rank(Count({<Winner={"Yes"}>}Team),4,2),Team))

[#3]

Thanks Sunny! would you please answer the second part of my question: how to find the count number of  wins for Golden Eagle tam?

[sunny_talwar · Accepted solution]

My previous expression for team was incorrect. Try this for team:

=FirstSortedValue({<Winner = {Yes}>}Team, -Aggr(Count({<Winner={Yes}>}Team),Team))

and this for count

=Count({<Team = {'$(=FirstSortedValue({<Winner = {Yes}>}Team, -Aggr(Count({<Winner={Yes}>}Team),Team)))'}, Winner={"Yes"}>} Points)

[Kushal_Chawda]

Create the Straight table

Dimension:

Team

Expression:

1) Rank

   Rank(Count({<Winner={Yes}>}Team),4)

2) # wins

   Count({<Winner={Yes}>}Team)

From the above straight table you will be able to know easily that which team won most of the games.

[#6]

Hi Kush, my requirement was restricted to represent it in a text box ..Thanks!