Top 25 using rank and/or aggr functions

[#1]

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.

[kkkumar82]

Any sample data would help us

[vinieme12]

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

[#4]

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

[#5]

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!

[MK_QSL]

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..

[Anonymous]

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

Exp:  sum(Sales)

[#8]

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".

[#9]

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

[MK_QSL]

Dimension

PERSON_ID

All other are expressions