Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
If you can use staright table, you get option of 'Others' in dimension limt tab, and get to use restricting top values.
Hi Stanislav,
You can use the "Dimension Limit" tab to display only 8 values.
Don't need Set analysis for that
best regards
Chris
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.
try Calculated Dimension and Use Rank function in Product to get top 10 Rank.
and no idea about others.
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
But how to make "Others" to be in last position of table?
In the sort tab use expression something like:
=Aggr(If(Rank(Sum(Amount))<11, Sum(Amount),-1), Product, Year)
It's not full answer as I guess.
How can I get 2 additional values specified manually in my 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