Announcements
cancel
Showing results for
Did you mean:
Creator

## show excluded values in table

Hi folks,

I have a list of customers, products, and sale values.

When I select a customer, I want a table showing a list of products that the customer hasn't bought.
I also would like to sort that table according to the most popular product (ie. products that generated the most revenue for the company in 2015 will be at the top or only showing top 10 products).

Thank you so much!

1 Solution

Accepted Solutions
Creator III

Hello!

Find my example, hope it will be helpful

7 Replies

Use Product as dimension and as expressions something like sum({<Customer=E(Customer)>}Sales). That will show you the Sales per Product that don't have Sales from the selected Customer. You can put the expression on top of the sort list on the Sort tab and choose Descending as sort order. On the Dimension Limits tab you can choose to show a Top X Products instead of all the Products.

talk is cheap, supply exceeds demand
Creator III

Hello!

Find my example, hope it will be helpful

Creator
Author

Thanks Gysbert, but its still not what I'm trying to do exactly.

For example, if Customer A bought product X and Y, and Customer B bought product Y and Z,

when I select Customer A, I want the table to only show product Z.

The suggestion above would show both product Y and Z since it's showing sales from all the customers except Customer A?

Try this one: sum({<Product=E({<Customer={'\$(=only(Customer))'}>}Product)>}Sales)

talk is cheap, supply exceeds demand
Creator
Author

Hey Tatsiana,

Do you mind explaining what you did?

Creator III

dimension is the product

expression is a flag

if(sum({1<CUSTOMER-={"\$(=getfieldselections(CUSTOMER))"}>}AMOUNT)

=sum({1} AMOUNT),1,0)

I compare sum sales all customer sum({1} AMOUNT) and customer without selected sum({1<CUSTOMER-={"\$(=getfieldselections(CUSTOMER))"}>}AMOUNT), if they are equal, than flag to showing = 1. So, this column is hidden and you can see just product.

but, if you try to select more than one customer expression doesn't work, cause my set analysis is good for one selection

Creator
Author

Hey Tatsiana, got it! Thank you!

Would it be possible to add a filter to it as well? Like if I want to filter by city or country. I can't figure out how to add the modifier into the set analysis.

Community Browser