13 Replies Latest reply: Feb 5, 2014 8:50 AM by Erica Whalley

# Products that the customer has not buy

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

CustomerExcluded Products
A1
B

1

B2
B6
C2
C3
D6

Regards,

Eleni

• ###### Re: Products that the customer has not buy

Hi Eleni, the best method is create the flag in the model which give the product was excluded or not for customer.

• ###### Re: Products that the customer has not buy

COUNT(Distinct TOTAL Product) - COUNT(DISTINCT Product)

• ###### Re: Products that the customer has not buy

Hi,

Thank you for your reply, but I dont want to find the count total, but the exact products. Assume that numbers in the second column are reffer to ProductID. More than that the only suggestion that works correctly for my data was the Count({\$<Customer = E({1<Product={'*'}>}) >} Distinct Product). Any way, I don't want to calculate the count so, it doesn't matter!

Any further ideas?

New e.g.

CustomerProduct not bought
AX
AY
BX
CY
CZ

Eleni

• ###### Re: Products that the customer has not buy

Hi Eleni,

Create a Straight Table with Dimensions

Customer

ProductID

with the Expression

FABS(SUM(Sales <=0))

• ###### Re: Products that the customer has not buy

Thank you for your suggestion Alan but I have already test it and it doesn't work.

Result:

Customer Product FABS(SUM(Sales <=0))
0

Am I missing something?

Regards,

Eleni

• ###### Re: Re: Products that the customer has not buy

Hi Eleni,

Regards

Alan

• ###### Re: Products that the customer has not buy

Create the Expression

COUNT(DISTINCT {<Sales = {"<=0"}>} Product)

• ###### Re: Products that the customer has not buy

Hi, if you want to count the products that every customer has not bought, you can try with something like this with E():

Count({\$<Customer = E({1<Product={'*'}>}) >} Distinct Product)

regards

• ###### Re: Products that the customer has not buy

Eleni,

The problem is that the Client-Product does not exist. There is no line. If you want (for one given line) display ALL products, you will display only the product of that customer. If you want to display the products whose sales = 0, you cannot becaus the lines are not in the database (normally, we load only what is necessary, it means the sales > 0).

See the report I have done with dates and customers.

Current clients:  concat(DISTINCT ClientID, ' | ')

All Clients of the date: concat(DISTINCT {1<DateID=, ClientID=>} ClientID, ' | ')

All clients of all dates: concat(DISTINCT {<ClientID= {"=sum({1} Sales)>0"}>} ClientID, ' | ')

And I get each time the same result.

If I want to get everything, I need to create a variable vAll= concat(DISTINCT {1} ClientID, ' ')  that will be interpreted. But that is just text. Cannot think we can create an object of type dataset so that we can an intersection with what we find for each line.

Perhaps, dathu is right, you need to do sth in the script.

I will be interested by the solution you get or you find by yourself.

Fabrice

• ###### Re: Products that the customer has not buy

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

• ###### Re: Products that the customer has not buy

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

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 .

• ###### Re: Products that the customer has not buy

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.

• ###### Re: Products that the customer has not buy

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