10 Replies Latest reply: May 25, 2012 9:07 AM by Jason Michaelides

Hello,

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.

• ###### Re: Doubt about rank()

Dimension = Country

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

(I think - bit rushed...)

See attached,

Jason

• ###### Re: Doubt about rank()

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.

• ###### Re: Doubt about rank()

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.

• ###### Re: Doubt about rank()

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

But I modified your qv with one exemple.

• ###### Re: Doubt about rank()

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?

• ###### Re: Doubt about rank()

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.

• ###### Re: Doubt about rank()

Hi Jason, thank you.

Yes, the ranking must be dinamic...

• ###### Re: Doubt about rank()

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

• ###### Re: Doubt about rank()

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.