topic Re: show excluded values in table in QlikView

show excluded values in table

ninnartx — Tue, 01 Mar 2016 03:36:17 GMT

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!

Re: show excluded values in table

Gysbert_Wassenaar — Tue, 01 Mar 2016 11:46:16 GMT

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.

Re: show excluded values in table

t_chetirbok — Tue, 01 Mar 2016 11:55:24 GMT

Hello!

Find my example, hope it will be helpful

Re: show excluded values in table

ninnartx — Wed, 02 Mar 2016 03:01:42 GMT

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?

Re: show excluded values in table

Gysbert_Wassenaar — Wed, 02 Mar 2016 15:27:24 GMT

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

Re: show excluded values in table

ninnartx — Thu, 03 Mar 2016 15:23:18 GMT

Hey Tatsiana,

Do you mind explaining what you did?

Re: show excluded values in table

t_chetirbok — Fri, 04 Mar 2016 06:51:22 GMT

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

Re: show excluded values in table

ninnartx — Sun, 06 Mar 2016 09:26:40 GMT

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.