Products that the customer has not buy

[eleni_theodorid]

Hi guys,

Is there any chance to have a table box or a chart which will show at the firt column the Customer and at the second all products that he hasn't buy, and how???

Customer	Excluded Products
A	1
B	1
B	2
B	6
C	2
C	3
D	6

Regards,

Eleni

[eleni_theodorid]

Hi Fabrice,

This is exactly my problem. Also I thought that is impossible not to do this at the layout. Is something that you could easy do with two list boxes, we could calculate sales of one product related with "not sales" of another, I could not imagine that there is no way for qlikview to calculate such a logical thing for a business user. I still hope!!!

Sent from Samsung Mobile

[#3]

Hi Eleni, create the another table with customer, product , flag (based on sales) and join this table to data model when necessary.

FladTable:

Load Customer, Product , If(TempSales>0 , 1, 0) AS flag ;

Load Customer, Product , Sum(Sales) as TempSales From FactTable group by Customer, Product ;

On the Layout: create the straight table with dimesions: Customer & if(flag=0,product)

Please post some sample qvw and data .

[#4]

Hi,

Eventually, if no distinct product ID has any character in common, there's a Concat trick which can provide you a list of not-bought products by customer, see mini-example in attached:

PurgeChar(Concat(DISTINCT TOTAL [Sold Products],','),Concat(DISTINCT [Sold Products]))

Apart from this, if you only have 1 straight table with customer and sold product for each sale, I agree you'd rather need something during the load. But if your data model contains reference tables with details about products and/or customers, we could do better.

Hope it helps.

[#5]

Hi

If you create a stand-alone table in the script with just the distinct products, you can stick that in a table against the customers and only return lines where there are no sales for that "new product field"= "old product field"

ie in the script:

Stand_Alone_product:

load distinct Product as Product2 resident salesTable;

then in the app, create a table chart with:

Dimension1: Customer

Dimension2: Product2

expression: sum(if(product= Product2,sales))=0

If you have checked "suppress zero values" in the presentation tab, then this will only return rows that are -1 ("true"), ie where the sum of sales =0

Does this work?

Erica