Products Never Bought

[azmeerrehan]

I have a customer data with sales for Products.   I have fields

Customer

Year

Product

Sales

I want to get the list of Products which were never bought by a customer

Lets say I have 3 Products and Customer A never bought Product no 3 so I want to see  out put as

Customer A , 3

Please Help

[maxsheva]

Hi,

You may try below

NoSales:

LOAD Customer,

           Year,

           Product,

           Sales

FROM datasource

WHERE Sales = 0;

It will give you output for all Customers and Products without sales.

[azmeerrehan]

HOw are you going to see if a customer never bought a certain product there will not be a Row for that Product against that customer so there is 0 Sales for that Product. The Product do exist thought bought by other customers. SO If Customer A bought Product Q and Customer B did not buy Product Q, then I expect the Output

Customer B   Q

[nsetty]

Add this as a measure in textbox

=if(GetSelectedCount(Customer,'','$')>0, Concat({1-$} Distinct Product,chr(10)))

[azmeerrehan]

I need a table not dependent on selection. I want to see all the products which a certain customer never bought, without making any selections

[dx_anupam]

Hi Rehan,

do you have any separate list of products? if yes you have to Concatenate it with customer then only its possible.

Regards,

Anupam

[tresesco]

There must be a more elegant expression than this, just not hitting right at the moment. Till somebody else come up with that, you could try like:

=Mid(

Replace(

Concat(DISTINCT Total Product, '|'),

      Concat(DISTINCT Product, '|'),

      ''

      )

    ,2

    )

[azmeerrehan]

I dont want the products concatenated. I would  like to see each product not bought, in a seperate row

[azmeerrehan]

Also as soon as I select a customer, never bought table goes blank

[ogautier62]

Hi,

maybe two ways :

1)

make a cross product of all customer/product

load separately distinct customer, then distinct product

and outer join (first one) load second one

you'll have all combination of customer/product,

so in front make a sheet with

Customer

Product

as dimensions

you can now add the condition sum(sales)=0

2)

load a new field Product2 as distinct Product

in front

a sheet with dimensions :

-Customer

- if(aggr(sum(if(Product = Product2, sales,0)),customer,Product2) >0,Product2)

more expensive in response time, and not so clean as 1/

regards