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.

Dimension = Country

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

(I think - bit rushed...)

See attached,

Jason

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.

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.

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

But I modified your qv with one exemple.

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?

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.

Hi Jason, thank you.

Yes, the ranking must be dinamic...

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

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.