15 Replies Latest reply: May 16, 2017 9:35 AM by David Pardoe

# Top 25 using rank and/or aggr functions

I can't see the wood for the trees. I have a PEOPLE table with a field SALES_LAST_PERIOD. I would like an expression that provides the rank for the people in terms of their SALES_LAST_PERIOD.

When I create a table and include a "measure" column with the expression: rank(SALES_LAST_PERIOD) I always get a 1 in the table for every row.

If I try to add a "dimension" column with the same expression I get "invalid dimension"

This seems a bit obvious to me?!

Ultimately I want to use set analysis to compare a metric for a PERSON with the avergae metric for the top 25 people based on their sales.

• ###### Re: Top 25 using rank and/or aggr functions

Any sample data would help us

• ###### Re: Top 25 using rank and/or aggr functions

OK. Here we go:

PERSON_ID,SALES_LAST_PERIOD,CALLS_LAST PERIOD

1,100,20

2,110,19

3,200,35

4,85,22

5,120,10

6,50,5

7,60,25

8,150,30

9,115,24

10,300,40

Question 1: what expression can I use to give:

PERSON_ID,RANK

1,7

2,6

3,2

4,8

5,4

6,10

7,9

8,3

9,5

10,1

Question 1A: how do I limit my table to only show the top 5:

PERSON_ID,RANK,SALES_LAST_PERIOD

10,1,300

3,2,200

8,3,150

5,4,120

9,5,115

Question 2: what expression can I use to give the following where the average(CALLS_LAST_PERIOD)=27.8 for the top 5 people

PERSON_ID,CALLS_DIFFERENCE_FROM_TOP_5

1,-7.8

2,-8.8

3,7.2

4,-5.8

5,-17.8

6,-22.8

7,-2.8

8,2.2

9,-3.8

10,12.2

Thanks!

• ###### Re: Top 25 using rank and/or aggr functions

For Rank

Rank(SUM({<PERSON_ID = {"=Rank(SUM(SALES_LAST_PERIOD))<=5"}>}SALES_LAST_PERIOD))

For Sales

SUM({<PERSON_ID = {"=Rank(SUM(SALES_LAST_PERIOD))<=5"}>}SALES_LAST_PERIOD)

For AVG Difference wrt your TOP 5 Call

[CALLS_LAST PERIOD] - AVG(Total {<PERSON_ID = {"=Rank(SUM(SALES_LAST_PERIOD))<=5"}>}[CALLS_LAST PERIOD])

Use PERSON_ID as dimension for all these..

• ###### Re: Top 25 using rank and/or aggr functions

For your first suggestion when I create a table and try and add a column it asks me if I want a measure or dimension. If I choose dimension and then use the syntax you suggest it tells me it is an "invalid dimension".

• ###### Re: Top 25 using rank and/or aggr functions

Dimension

PERSON_ID

All other are expressions

• ###### Re: Top 25 using rank and/or aggr functions

I am being daft here but I don't know what you mean - how do I differentiate "dimensions" from "expressions"

• ###### Re: Top 25 using rank and/or aggr functions

If you create a new chart

You can see there are several tabs..

Dimension tab use PERSON_ID

and Expression tab use those three expressions..

• ###### Re: Top 25 using rank and/or aggr functions

So when I create a chart I can see how this works...

If I create a table (not a chart) it acts strangely. I can add a column to my table as a "measure" and only the top 5 rows are returned but the value in the column is always 1 - not the rank!

• ###### Re: Top 25 using rank and/or aggr functions

Do one thing... Whatever QVW you have created, attach it here as sample.

• ###### Re: Top 25 using rank and/or aggr functions

OK - I think I am there.

One minor amend I want to make is that I want the selection to be the top 5 regardless of the selections I make.

I thought I could amend this expression as suggested earlier

[CALLS_LAST PERIOD] - AVG(Total {<PERSON_ID = {"=Rank(SUM(SALES_LAST_PERIOD))<=5"}>}[CALLS_LAST PERIOD])

To:

[CALLS_LAST PERIOD] - AVG(Total {<PERSON_ID = {"=Rank(SUM(1<SALES_LAST_PERIOD))<=5"}>}[CALLS_LAST PERIOD])

But this does not seem to work - how should I adjust the expression to ignore current selections?

• ###### Re: Top 25 using rank and/or aggr functions

there are many similar threads that you can search on the community

Calculating TOP 10 VALUES in PIVOT TABLE

Top 10 and Worst 10 | Qlik Community

• ###### Re: Top 25 using rank and/or aggr functions

I have tried searching quite a few posts and still can't get what I need! Posting example data separately.

• ###### Re: Top 25 using rank and/or aggr functions

Dimension ---if( aggr( Rank( Sum(Sales)),PERSON_ID) <=25, PERSON_ID) as Person_id

Exp:  sum(Sales)

• ###### Re: Top 25 using rank and/or aggr functions

I'm not quite sure I understand what you mean by "Dimension ---" and "Exp:"

• ###### Re: Top 25 using rank and/or aggr functions

in the chart dimension use   if( aggr( Rank( Sum(SALES_LAST_PERIOD)),PERSON_ID) <=25, PERSON_ID)

in the expression - Sum(SALES_LAST_PERIOD)

here you will get top 25 sales persons

if( aggr( Rank( Avg(SALES_LAST_PERIOD)),PERSON_ID) <=5, PERSON_ID)

here you will get top 5 Avg persons