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

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

MK_QSL
MVP
MVP

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

Anonymous
Not applicable
Author

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


Not applicable
Author

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!

MK_QSL
MVP
MVP

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

Not applicable
Author

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?