12 Replies Latest reply: Feb 9, 2016 5:22 AM by Onno van Knotsenburg

# Using Rank and aggr Function

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

• ###### Re: Using Rank and aggr Function

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.

• ###### Re: Using Rank and aggr Function

Thank you Manish

Worked perfectly

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

• ###### Re: Using Rank and aggr Function

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

• ###### Re: Using Rank and aggr Function

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

• ###### Re: Using Rank and aggr Function

Use the below expression in textbox,

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

• ###### Re: Using Rank and aggr Function

It works

Thank you for the help

• ###### Re: Using Rank and aggr Function

Hi Onno

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

• ###### Re: Using Rank and aggr Function

Please note that the post I picked is a suggestion. If you think another post was the correct answer for you.

If for whatever reason you do not see that button in the image, try a different browser. If it still does not work, let me know which post and I will do it for you.

Kind regards,

Onno

Community Team member

• ###### Re: Using Rank and aggr Function

Thank you.... I changed browser and could still not see the Correct Answer Icon

The 1st Post and last post were correct

• ###### Re: Using Rank and aggr Function

Strange . Probably a silly bug somewhere.

Anyway,

Since I can only flag 1 answer correct per topic, I flagged the first one.

As a result, the topic is now flagged answered.

Have a great day!

• ###### Re: Using Rank and aggr Function

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)