# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
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
Highlighted
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
Highlighted
Creator III

Hello, Mattew.

Try this as your calculated dimension:

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

Regards.

Highlighted
Not applicable

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

Highlighted
Not applicable

can you attach a sample qvw please?

Highlighted
Not applicable

Hi,

Sample qvw attached.

Regards,

Matthew

Highlighted
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.

Highlighted
MVP & Luminary

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

-Rob

Highlighted
Not applicable

Hi Bruno,

Thanks for your help this did the trick.

Kind Regards,

Matthew

Highlighted
Not applicable

Hi Rob,

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

Kind Regards,

Matthew

Highlighted
MVP & Luminary

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