Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
bruno_montenegr
Contributor III

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

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
bruno_montenegr
Contributor III

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

Hello, Mattew.

Try this as your calculated dimension:

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

Regards.

Not applicable

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

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

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

can you attach a sample qvw please?

Not applicable

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

Hi,

Sample qvw attached.

Regards,

Matthew

bruno_montenegr
Contributor III

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

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

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

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

-Rob

Not applicable

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

Hi Bruno,

Thanks for your help this did the trick.

Kind Regards,

Matthew

Not applicable

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

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

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

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