Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
soniasweety
Master
Master

region wise

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

1 Solution

Accepted Solutions
jyothish8807
Master II
Master II

Just copy the same expression and replace you expression for service and hardware

Refer the attachment.

Best Regards,
KC

View solution in original post

16 Replies
pooja_prabhu_n
Creator III
Creator III

Hi,

try this

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

Hope this helps

Thanks,

Pooja

tresesco
MVP
MVP

Try expression like:

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

soniasweety
Master
Master
Author

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
Master
Master
Author

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
Master II
Master II

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

Best Regards,
KC
pooja_prabhu_n
Creator III
Creator III

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
Master II
Master II

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

Best Regards,
KC