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

[#1]

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 $

[Anonymous]

Hello, Mattew.

Try this as your calculated dimension:

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

Regards.

[#3]

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

[#4]

can you attach a sample qvw please?

[#5]

Hi,

Sample qvw attached.

Regards,

Matthew

[Anonymous · Accepted solution]

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.

[rwunderlich]

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

-Rob

[#8]

Hi Bruno,

Thanks for your help this did the trick.

Kind Regards,

Matthew

[#9]

Hi Rob,

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

Kind Regards,

Matthew

[rwunderlich]

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