topic Re: Determining Rank() within Set analysis in QlikView

Determining Rank() within Set analysis

Fri, 11 Mar 2016 10:34:31 GMT

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)

Re: Determining Rank() within Set analysis

sunny_talwar — Fri, 11 Mar 2016 10:37:14 GMT

Try this:

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

Re: Determining Rank() within Set analysis

Fri, 11 Mar 2016 11:04:20 GMT

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

Re: Determining Rank() within Set analysis

sunny_talwar — Fri, 11 Mar 2016 11:16:38 GMT

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)

Re: Determining Rank() within Set analysis

Kushal_Chawda — Fri, 11 Mar 2016 11:38:04 GMT

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.

Re: Determining Rank() within Set analysis

Fri, 11 Mar 2016 11:58:40 GMT

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