3 Replies Latest reply: Jan 31, 2012 2:16 AM by Médéric Eloy

# Calculate the Top 5 Vendors' revenue in a field, not in a chart

Hi All,

I need your help.I would like to calculate in a text field the sum of the revenues of my top 5 vendors.
No problem to do it in a pivot chart using the Rank function like : if(Rank(sum(Revenue))<=5,Sum(Revenue),0)

In the pivot chart, I can order by rank and calculate the sum of the Top 5.

I would like to do it now in a text field with a formula like : Sum (  if(Rank(sum(Revenue))<=5,Sum(Revenue),0) )
But it doesn't work.

is there some that could help me?
Many thanks.

• ###### Calculate the Top 5 Vendors' revenue in a field, not in a chart

I think this won't work with a simple statement in textbox, as you don't have dimensions to refer to. I would suggest using a table-chart with invisible borders&lines instead.

Regards,

Edgar

• ###### Re: Calculate the Top 5 Vendors' revenue in a field, not in a chart

Hi,

Try this.

Sum (  if(Rank(aggr(sum(Revenue), DImensionName))<=5,Sum(Revenue),0) )

Change the dimention Name with your dimension.

Regards,

Kaushik Solanki

• ###### Re: Calculate the Top 5 Vendors' revenue in a field, not in a chart

Thanks all,
I've forgotten my text field as suggested by Edgar.
I've created  a table-chart using Kaushik's formula as dimension.
I have now a beautiful table-chart with Top, Top10 and Top15 and it works very well.
Now it's just a question of cosmetic.