Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

15 Replies
kkkumar82
Specialist III
Specialist III

Any sample data would help us

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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

Not applicable
Author

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

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
Not applicable
Author

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

Exp:  sum(Sales)

Not applicable
Author

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

Not applicable
Author

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

MK_QSL
MVP
MVP

Dimension

PERSON_ID

All other are expressions