Set analisys for top values in pivot table

[stantrolav]

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:

Product	sum(Amount)
INHALIPT	2356
ANTI ANGIN	2145
HEXORAL	1876
CAMETON	1500
STOPANGIN	1400
TANTUM VERDE	1238
STREPSILS	786
FALIMINT	652
SEPTOLETE	23
STREPSILS PLUS	6
Others	769

Could someone help with this simple task?

[tresesco]

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

[#3]

Hi Stanislav,

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

Don't need Set analysis for that

best regards

Chris

[stantrolav]

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.

[israrkhan]

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

and no idea about others.

[tresesco]

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

[stantrolav]

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

[tresesco]

In the sort tab use expression something like:

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

[stantrolav]

It's not full answer as I guess.

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

[#10 · Accepted solution]

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