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

Using Rank

Hi all,

Below is the table i am using

  

Customer name        Sale
A1938
A2324
A3302
A4463
A5427
A60
A7277
A8387
A90
A10183

i need create two  tables for top 5 and bottom 5 . In bottom 5 i need to exclude whose customer having "zero" in their sales    . User is flexible to select any top n or bottom  n b/w 5 (i.e., top 1-2 or bottom 2-3 or top 3-4)  .

Could any one help me .

Thanks in advance

1 Solution

Accepted Solutions
swuehl
MVP
MVP

You can add a range to the condition like

=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(Max) and Rank(-Sum({<Sales = {'>0'}>} Sales))>=$(Min)"}>} Sales)

View solution in original post

9 Replies
swuehl
MVP
MVP

Create two tables, Customer name being dimension in both.

Then as expression

=Sum({<CustomerName = {"Rank(Sum(Sales))<=$(vTopRank)"}>}Sales)

resp.

=Sum({<CustomerName = {"Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(vBottomRank)"}>} Sales)

Not applicable
Author

Hi Stefan,

Thanks for reply..

could you kindly provide me the expressions for $(vTopRank) and $(vBottomRank).

But however, the above expression helps me to filter out the data according to top or bottom N. but my requirement is  if i wanna to see only top 2 and 3 customer sales or bottom 2 and 3 customer sales .

Thanks in advance

swuehl
MVP
MVP

vTopRank and vBottomRank are just variables that hold a number (Top or Bottom N customers).

I thought that's what you intended (creating e.g. a slider so the user can select N)

I've  also noticed I forgot the leading equal sign in the set modifier to indicate an advanced search:

=Sum({<CustomerName = {"=Rank(Sum(Sales))<=$(vTopRank)"}>}Sales)

resp.

=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(vBottomRank)"}>} Sales)

Not applicable
Author

Hi stefan,

Thanks for previous reply..

But when i using ur bottom expression(for bottom 4 customer) . the output is show below

t1.PNG

but the user wanna to only bottom 2 to 4 records

t2.PNG

that is, he wanna to see only

t3.PNG

Kindly help me ..

Thanks in advance

swuehl
MVP
MVP

You can add a range to the condition like

=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(Max) and Rank(-Sum({<Sales = {'>0'}>} Sales))>=$(Min)"}>} Sales)

Not applicable
Author

hi,

Thanks a lot ...

Not applicable
Author

Hi

If we want to see along with zero values for bottom customers ?

i have removed >0 from the set analysis but i m not getting correct values .

Thanks in advance

Not applicable
Author

Hi ,

i have a varable

vProdVar= sum(aggr(if(IsNull(Map_Customername) or not( IsNull(Map_Key)),'',TotalMargin),Key,[customer name]))+sum(aggr(if(IsNull(Mapping_Customername) or not( IsNull(Mapping_Key)),'',[Total margin]*-1),Key,[customer name]))

in straight table .. i m using the below dimension  for selecting bottom customers.

AGGR(IF(Rank(-$(vProdVar),4)>=$(vMin) AND

          (Rank(-$(vProdVar),4)<=$(vMax)) ,[customer name] ),[customer name] )

i am getting error with rank function . Could you kindly help me ..

Thanks advance

swuehl
MVP
MVP

Not sure what your complex variable should return.

Could you post a small sample QVW that demonstrates also your data model?