Qlik Community

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 
Search instead 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?

Thanks for your time,

Matthew

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

Accepted Solutions
Highlighted
Creator III
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.

View solution in original post

9 Replies
Highlighted
Creator III
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
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.

View solution in original post

Highlighted
MVP & Luminary
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
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