Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Add this as a measure in textbox
=if(GetSelectedCount(Customer,'','$')>0, Concat({1-$} Distinct Product,chr(10)))
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
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
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
)
I dont want the products concatenated. I would like to see each product not bought, in a seperate row
Also as soon as I select a customer, never bought table goes blank
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