Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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???
Customer | Excluded Products |
---|---|
A | 1 |
B | 1 |
B | 2 |
B | 6 |
C | 2 |
C | 3 |
D | 6 |
Regards,
Eleni
Hi Eleni, the best method is create the flag in the model which give the product was excluded or not for customer.
COUNT(Distinct TOTAL Product) - COUNT(DISTINCT Product)
Create the Expression
COUNT(DISTINCT {<Sales = {"<=0"}>} Product)
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
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.
Customer | Product not bought |
---|---|
A | X |
A | Y |
B | X |
C | Y |
C | Z |
Thanks in advance,
Eleni
Hi Eleni,
Create a Straight Table with Dimensions
Customer
ProductID
with the Expression
FABS(SUM(Sales <=0))
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
Hi Eleni,
Please see the attached file
Regards
Alan
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