region wise

[soniasweety]

Hi all,

Ruben Marin

i want to show in pivot table top5 customers by each region

i have 3 regions  --- so in pivot table i want to show  total 15 customers each region 5 customers.

how can i do this?

Thanks

Sony

[pooja_prabhu_n]

Hi,

try this

=If(Rank(Sum(Sales), 1, 1) <= 5, Sum(Sales))

Hope this helps

Thanks,

Pooja

[tresesco]

Try expression like:

If(Rank(Sum(Sales))<6, Sum(Sales))

[soniasweety]

thanks  but  i  have to measures

i cant put sum(sales)

dimensions:  

customer

satge

Measure

sum({<type={'service'}>amount)

sum({<type={'Harware'}>amount)

the above am using  how can i do this?

[rubenmarin]

Hi Sony, both answers (Pooja and Tresesco) seems to work. Just use your measure instead of sum(sales). That doesn't works?

[soniasweety]

not working

it is showing for each region 15 customers

i

[rubenmarin]

I tried to reproduce it on Pooja's sample but only 5 customers per region are shown. Can you upload a sample or change the attached to show 15 customers by each region?

If(Rank(Sum(Sales), 4, 1) -> '4' to keep only one when there are different customers in the same rank.

[jyothish8807]

Hi Sony,

If you dont want to  modify the expression, then try this as dimension.

Replace customer with this expression:

=Aggr(If(Aggr(Rank(Sum(Sales),1,1),Region,Customer)<= 5,Customer),Region,Customer)

Exp: sum(Sales)

Br,

KC

[pooja_prabhu_n]

Yeah. It won't work when you have two expressions, If the top 5 customers are different for both the type, then it will display top5 for type=service and top 5 customers for type=Harware.

If you just want top 5 for each region you have to use expression like this:

=If(Rank(sum( {<Type={'service','Harware'}>}amount)) <= 5, sum( {<Type={'service','Harware'}>}amount))

Thanks,

Pooja

[jyothish8807]

Also click on suppress null values.

But it is always recommended to modify the expressions rather using calculated dimensions. So the above mentioned solutions are more optimized

Br,

KC