# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean:
Not applicable

## Need to display product for top 100 customers based on sales?

Hi,

I have a database consisting of two tables linked by Invoice No (similar to the below). I would like to create a calculated dimension in a straight table to display all the Product for the top 100 customers based on their sales?

Matthew

Table 1
Invoice No
Customer
Table 2
Invoice No
Product
Sales \$
1 Solution

Accepted Solutions
Creator III

Hello, Matthew.

I was able to solve your problem not with Calculated Dimensions but with Set Analysis. Use Product as the dimension and model your expressions like this one:

Sum({\$<Product = P({1<Customer = {'\$(=Concat(Aggr(If(Rank(Sum([Sales \$])) <= 100, Customer), Customer), Chr(39)&','&Chr(39)))'}>} Product)>} [Sales \$])

Attached is the QVW.

Cheers.

9 Replies
Creator III

Hello, Mattew.

Try this as your calculated dimension:

=Aggr(If(Rank(Sum([Sales \$])) <= 100, Product), Customer)

Regards.

Not applicable
Author

Hi Bruno,

Thanks for the reply, unfortunately that returned a null value.

I am currently using the below formula to display the top 100 customers, basically I want the dimension to display all the products that the customers calculated with the below formula purchased. But I am not sure how to expand the formula to achieve this.

=aggr(if(rank(sum([Sales \$]))<=100,Customer),Customer)

Regards,

Matthew

Not applicable
Author

can you attach a sample qvw please?

Not applicable
Author

Hi,

Sample qvw attached.

Regards,

Matthew

Creator III

Hello, Matthew.

I was able to solve your problem not with Calculated Dimensions but with Set Analysis. Use Product as the dimension and model your expressions like this one:

Sum({\$<Product = P({1<Customer = {'\$(=Concat(Aggr(If(Rank(Sum([Sales \$])) <= 100, Customer), Customer), Chr(39)&','&Chr(39)))'}>} Product)>} [Sales \$])

Attached is the QVW.

Cheers.

MVP

Have you considered using dimension limits instead of a calc dimension?

-Rob

Not applicable
Author

Hi Bruno,

Thanks for your help this did the trick.

Kind Regards,

Matthew

Not applicable
Author

Hi Rob,

No, I haven't looked into dimension limits too much, but I will keep this in mind in future.

Kind Regards,

Matthew

MVP

I recommend you check out Dimension Limits. It's a lot easier than Set Analysis or Calculated Dimensions. It's right there in the QV11 chart properties next to the Dimensions pane.

-Rob

Community Browser