topic Re: Using Rank and aggr Function in QlikView

Using Rank and aggr Function

Mon, 08 Feb 2016 07:44:24 GMT

Hi There

I have the following data

PhaseNo, Tackles Missed

1,2

1,1

2,4

2,1

3,2

4,5

5,1

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

MK_QSL — Mon, 08 Feb 2016 09:08:10 GMT

Create a Straight Table

Dimension

PhaseNo

Expression

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

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

Whatever is applicable to your application.

Re: Using Rank and aggr Function

Mon, 08 Feb 2016 09:19:14 GMT

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

tamilarasu — Mon, 08 Feb 2016 09:37:22 GMT

You can also use,

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

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

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

Sample attached against your data.

Re: Using Rank and aggr Function

MK_QSL — Mon, 08 Feb 2016 13:27:16 GMT

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

Re: Using Rank and aggr Function

Tue, 09 Feb 2016 05:20:20 GMT

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

tamilarasu — Tue, 09 Feb 2016 07:30:37 GMT

Use the below expression in textbox,

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

Re: Using Rank and aggr Function

Tue, 09 Feb 2016 09:48:57 GMT

It works

Thank you for the help

Re: Using Rank and aggr Function

oknotsen — Tue, 09 Feb 2016 09:54:26 GMT

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

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

Re: Using Rank and aggr Function

Tue, 09 Feb 2016 09:58:02 GMT

Hi Onno

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

Re: Using Rank and aggr Function

oknotsen — Tue, 09 Feb 2016 10:06:24 GMT

Hi. Happy to help you with that. Please see attached picture .

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

Tue, 09 Feb 2016 10:20:08 GMT

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

oknotsen — Tue, 09 Feb 2016 10:22:19 GMT

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!