10 Replies Latest reply: Oct 30, 2013 10:12 AM by Stanislav Strogiy

# Set analisys for top values in pivot table

How can I bring TOP 8 values for the given expression and 2 additional values specified manually in the pivot table? I think i should use set analisys in dimension or expression.

I got 25 values of Product. To every product i have Amount. In pivot tabe i used Product as dimension and expression sum(Amount).

How can i get something like this:

Productsum(Amount)
INHALIPT2356
ANTI ANGIN2145
HEXORAL1876
CAMETON1500
STOPANGIN1400
TANTUM VERDE1238
STREPSILS786
FALIMINT652
SEPTOLETE23
STREPSILS PLUS6
Others769

Could someone help with this simple task?

• ###### Re: Set analisys for top values in pivot table

If you can use staright table, you get option of 'Others' in dimension limt tab, and get to use restricting top values.

• ###### Re: Set analisys for top values in pivot table

Hi Stanislav,

You can use the  "Dimension Limit" tab to display only 8 values.

Don't need Set analysis for that

best regards

Chris

• ###### Re: Set analisys for top values in pivot table

There no Dimension Limit for Pivot table. I cant use "staright table". Sorry tresesco.

I cant use "straight table" because I have 2 dimensions. Forgot to mark it. My second dimension is Years. In the end i want to see Top 10 Products (we'll use sum(Total Amount) only for Product dimension) for every year in 1 table.

• ###### Re: Set analisys for top values in pivot table

try Calculated Dimension and Use Rank function in Product to get top 10 Rank.

and no idea about others.

• ###### Re: Re: Set analisys for top values in pivot table

PFA for sample.

Use expression like:

=Aggr(If(Rank(Sum(Amount))<11, Product, 'Others'),  Product, Year)

as calculated dimension for Product for your case and the in sort tab, use Sum(Amount) ->descending

• ###### Re: Set analisys for top values in pivot table

But how to make "Others" to be in last position of table?

• ###### Re: Re: Set analisys for top values in pivot table

In the sort tab use expression something like:

=Aggr(If(Rank(Sum(Amount))<11, Sum(Amount),-1),  Product, Year)

• ###### Re: Re: Set analisys for top values in pivot table

It's not full answer as I guess.

How can I get 2 additional values specified manually in my table?

• ###### Re: Set analisys for top values in pivot table

In the if statement, add your 2 products:

=

Aggr(If(Rank(Sum([Amount]))<11 or Product='XXX' or Product='YYYY', Product, 'Others'), Product)

In other words, either your product belong to the top 10 or is XXXX or is YYYY you will get it, if not it will be sum up in Others

Fabrice

• ###### Re: Set analisys for top values in pivot table

That's it. Ok. Here is final answer from AUNEZ FABRICE.

Dimension:

=Aggr(If(Rank(Sum([Amount]))<=8 or Product='microscope' or Product='backgammon', Product, 'Others...'), Product)

Sort order:

=Aggr(If(Rank(Sum([Amount]))<1000, Sum([Amount]),-1), Product)

We use 1000 in sort order to specify that Others... should be the last from at least of 1000 values.