Get the products not sold to a customer

[ali_hijazi]

Hello 
got a sales dashboard with a fact table and salesmen and products and customers as dimensions
suppose in 2020 June salesman X sold Customer A 4 (P1,P2,P3, and P4)products and suppose I have 10 products (p1, ...p10)
is there a way to display in a pivot table that SalesMan X for Customer A he didn't sell P5, P6,... and p10?

Kindly advise
@sunny_talwar 

[Kushal_Chawda]

You should be able to show the products in different chart with just product as dimension but in same chart as it won't be possible to show excluded value against selected value in front end. Look at the below thread

https://community.qlik.com/t5/QlikView-App-Development/show-excluded-values-in-table/m-p/1055536

You would be able to do it via script but it will involve complexity and of course with limitation because you won't be able to show it dynamically based on selections. 

[sunny_talwar · Accepted solution]

You can build this using a ValueList function, but what are you trying to get using this? There won't be any value associated with it, correct? because this combination doesn't exists in your data?

=ValueList($(=Concat({1<PRODUCT_CODE = E(PRODUCT_CODE)>}Aggr(Only({1<PRODUCT_CODE = E(PRODUCT_CODE)>} Chr(39) & PRODUCT_CODE & Chr(39)), PRODUCT_CODE), ',')))

[ali_hijazi]

can't we have a list for customer1 and a list for customer2 ?
in Apr2020 customer2 bought only p1 while customer1 bought p1,p2,p3, and p4
so the output would be for customer1 p5,p6,p7,p8,p9, and p10
while for customer2 it would be p2,p3,p4,p5,p6,p7,p8,p9, and p10

I know this valuelist gets all excluded products regardless of any aggregation

kindly advise @sunny_talwar 

[sunny_talwar]

Not unless you make script modifications where you create these missing combinations and flag them. Also, this is not just because of ValueList, this is also because of your use of p() and e()... p() and e() will give you exclusion across the board and not by customer.

[ali_hijazi]

so it works if user is obliged to selecct one customer at a time otherwise teh result won't be accurate

[sunny_talwar]

that is correct