Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Partner
Partner

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

Tags (3)
13 Replies
Not applicable

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.

MVP
MVP

Re: Products that the customer has not buy

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

rustyfishbones
Honored Contributor II

Re: Products that the customer has not buy

Create the Expression

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

Partner
Partner

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)

adjusting this to your name fields may work,

regards

Partner
Partner

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

Thanks in advance,

Eleni

rustyfishbones
Honored Contributor II

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

Partner
Partner

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

rustyfishbones
Honored Contributor II

Re: Re: Products that the customer has not buy

Hi Eleni,

Please see the attached file

Regards

Alan

Not applicable

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