show top 3 values per first dimension

[chriscools]

hello,

i'm creating a pivot table with 2 dimensions:

-sales family

-art code

as a value i have the sum of sales of the selected month.

now i would like to show per sales family only the top 3 articles with it's sales, is this possible?

thanx!

grtz,

chris

[MayilVahanan]

Hi

Try like this

Expression tab:

=If(Rank(Sum(sales)) <=3, Sum(Sales))

Then suppress null values in dimesion tab

Hope it helps

[chriscools]

Hello,

thank you for your reply!

and how should i mingle it with the formula i had in mind with set analysis?

Sum ({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}>}LineSalesAmount) 

thanx!

chris

[MayilVahanan]

Hi

Try like this

=if(rank(

Sum ({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}>}LineSalesAmount) ) < = 3,

Sum ({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}>}LineSalesAmount) )

Hope it helps

[chriscools]

Hello,

thank you for your help, i got the formula to work!

it only calculates the top 3 articles for each sales family.

but i can not let it show only the top 3 ones...

if i select "suppres null values" under the dimension it doesn't  change...

could you perhaps have a look at it?

thank you very much!

grtz,

chris

[#6]

use the dimension limits tab

to filter the top 3 values

[chriscools]

Hey,

that doesn't work...

that way it gives me the top 3 values of the dimensions, but i wan't for each dimension only the top 3 calculated values are shown.

and with the expression provided by Mayil it only get value for the top 3 articles and the rest gets a null value.

but then i can not get to hide these null-values...

chris

[MayilVahanan · Accepted solution]

Hi

Are you expect like the attached file?

[chriscools]

indeed, that's exactly what i want! 🙂

could you please explain me what i was doing wrong?

thanx!!!

[MayilVahanan]

Hi

for ex:

Aggr(Rank(Sum(Sales)),Product,SkuCode)

it calculate the rank for each product and skucode in that product..

if(Aggr(Rank(Sum(Sales)),Product,SkuCode) <= 3, Sum(Sales)) //not overall rank..

rank(sum(sales)) calculate the rank for overall dimension.. so only it shows first three ranking values n for others it display null..