Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ninnartx
Creator
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
t_chetirbok
Creator III
Creator III

Hello!

Find my example, hope it will be helpful

View solution in original post

7 Replies
Gysbert_Wassenaar

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
t_chetirbok
Creator III
Creator III

Hello!

Find my example, hope it will be helpful

ninnartx
Creator
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?

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
ninnartx
Creator
Creator
Author

Hey Tatsiana,

Do you mind explaining what you did?

t_chetirbok
Creator III
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  

ninnartx
Creator
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.