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: 
eleni_theodorid
Partner - Creator
Partner - Creator

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

13 Replies
Not applicable

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

MK_QSL
MVP
MVP

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

rustyfishbones
Master II
Master II

Create the Expression

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

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

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)

adjusting this to your name fields may work,

regards

eleni_theodorid
Partner - Creator
Partner - Creator
Author

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

Thanks in advance,

Eleni

rustyfishbones
Master II
Master II

Hi Eleni,

Create a Straight Table with Dimensions

Customer

ProductID

with the Expression

FABS(SUM(Sales <=0))

eleni_theodorid
Partner - Creator
Partner - Creator
Author

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

rustyfishbones
Master II
Master II

Hi Eleni,

Please see the attached file

Regards

Alan

Not applicable

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