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

Doubt about rank()

Hello,

Please, can someone help me about a rank() graph?

I have a table with fields: Country, Seller, Amount, Discount.

How I do a graph showing for each Country, Sum(Amount) of top ten Sellers (ranking about Amount sold on that Country) and Sum(Discount) of top ten Sellers (ranking about Amount sold on that Country)?

I think it works by using rank() and aggr() commands, but I can't.

Thank you very much.

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

I went for a swim and had a Eureka! moment...we need to put the Aggr() and Rank() into a dynamic dimension for Country, not seller.  Simple straight table:

Dimension: =Aggr(if(Rank( Sum(Amount))<=10,  Country),Country,Seller) - you will need to tick "Suppress when Null"

Expression: Sum(Amount)

See attached.

There's always a way...

Jason

View solution in original post

10 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Dimension = Country

Expression = Sum({<Seller={"=Rank(Sum(Amount))<=10"} >} Amount)

(I think - bit rushed...)

See attached,

Jason

Not applicable
Author

Hi Jason, thank your answer.

I think I didn't explain it very well. What I mean, I need to show all countries, and for one country y need to show total amount of top ten sellers, but ranking about sells only on than country.

I tried with somethink like:
Dimension: Country
Expresion: Sum( {$<Seller= {"=rank(aggr(sum(Amount),Seller)) <= 10"}>} Amount)

But it shows amount only for whole top ten sellers, Instead of amount of top ten sellers of each Country.

Thank you very much.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Hmm...I'll have another look when I get to my desk. Can you post a sample of your app to make it easier please.

Not applicable
Author

Sorry, Jason, I can't. Company policy.

But I modified your qv with one exemple.

Not applicable
Author

I can do this if I include the Seller dimension, but I can't totalize, and when I colapse Country dimension it doesn't work.


Please, does someone know to do this?

Jason_Michaelides
Luminary Alumni
Luminary Alumni

I'm at the same place. Got it working in a pivot with seller dimension expanded but not collapsed. If only 1 country is selected it works OK.  This is because Set Analysis only calcluates once per chart so the list of sellers to include is global.

Tricky one - there will be a way!

Is the ranking dependent upon user selections?  If not you could identify the top 10 sellers per country in the script and flag them.

Not applicable
Author

Hi Jason, thank you.

Yes, the ranking must be dinamic...

Jason_Michaelides
Luminary Alumni
Luminary Alumni

I went for a swim and had a Eureka! moment...we need to put the Aggr() and Rank() into a dynamic dimension for Country, not seller.  Simple straight table:

Dimension: =Aggr(if(Rank( Sum(Amount))<=10,  Country),Country,Seller) - you will need to tick "Suppress when Null"

Expression: Sum(Amount)

See attached.

There's always a way...

Jason

Not applicable
Author

Thank you very mutch!!

I tried with dimension too, editing Country dimension and I could not. But it is perfect.

Thank you one more time.