Using Rank and aggr Function

[#1]

Hi There

I have the following data

PhaseNo, Tackles Missed

1,2

1,1

1,1

2,4

2,1

3,2

4,5

5,1

5,1

6,2

6,2

6,1

I need to rank PhaseNo by sum of Tackles Missed (i.e. I need to know which Phase has the most Tackles Missed by rank)

Thanks

Kevin

[MK_QSL · Accepted solution]

Create a Straight Table

Dimension

PhaseNo

Expression

NUM(Rank(TOTAL SUM([Tackles Missed]),4))

or

NUM(Rank(TOTAL COUNT([Tackles Missed]),4))

Whatever is applicable to your application.

[#3]

Thank you Manish

Worked perfectly

Is there a way to create the same rank without a dimension (due to the layout of the report)

[tamilarasu]

You can also use,

Aggr(Rank(Sum([Tackles Missed]),4,1),PhaseNo)

or

Num(Aggr(Rank(Sum([Tackles Missed]),4),PhaseNo))

or

Aggr(Rank(Sum([Tackles Missed]),4),PhaseNo)

Sample attached against your data.

[MK_QSL]

How you want the output without dimension? Can you please provide here?

[#6]

I am looking to find rank the Phases with the most tackles using a text object due to the layout of the report

I can only show 1 Phase at a time. From there I am looking to use rank as a heatmap

[tamilarasu]

Use the below expression in textbox,

=Only({<PhaseNo = {"=Num(Aggr(Rank(Sum([Tackles Missed]),4),PhaseNo))=1"}>}PhaseNo)

[#8]

It works

Thank you for the help

[oknotsen]

If your question is now answered, please flag the Correct Answer.

If not, please tell us what help you still need with this question.

[#10]

Hi Onno

Yes sure... Where do I mark the answer as correct