Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have these data:
N°Order | Product | ProductPrice | Product Type | CustomerID |
---|---|---|---|---|
11111 | Pen | 1,00 | Supplies | 1 |
11111 | Rubber | 4,00 | Supplies | 1 |
22222 | Paper | 3,00 | Supplies | 2 |
33333 | Paper | 3,00 | Machine | 1 |
44444 | Bag | 15,00 | Machine | 3 |
55555 | Pencil | 2,00 | Supplies | 4 |
55555 | Pen | 1,00 | Supplies | 4 |
I would select only the customers whom have bought only "Product Type: Supplies" and never Machines.
In this case customer 2 and 4
I think it is possible with Set Analysis, but I don't know how.
Thanks
Try maybe something like
=concat(distinct
{<CustomerID=p({<[Product Type]={Supplies}>})*e({<[Product Type]={Machine}>})>}
CustomerID,', ')
edit:
Even the e()-part in the set modifier could be enough for your setting with two product types.
Hi,
That should be something like
Count({< [Product Type] = {'Supplies'} > - < [Product Type] = {'Machine'} >} CustomerID)
Hope that helps.
Miguel
Try maybe something like
=concat(distinct
{<CustomerID=p({<[Product Type]={Supplies}>})*e({<[Product Type]={Machine}>})>}
CustomerID,', ')
edit:
Even the e()-part in the set modifier could be enough for your setting with two product types.
As you can see, both of you are right, the small difference is that swuehl's espression includes also the data without relation on the other load charts.
even more I have also a Year field, how I can exclude this selection?
You can disregard a selection in a Year field by listing it in the set modifier field list, but without using a element set expression like
{$<Year= >}
or in my example above maybe like
{<CustomerID=p({<[Product Type]={Supplies}, Year= >})*e({<[Product Type]={Machine}, Year= >})>}
or even
{<CustomerID=p({<[Product Type]={Supplies}, Year= >})*e({<[Product Type]={Machine}, Year= >}), Year= >}
The first example only disregards selections in year for the field selection of CustomerID but it should still consider selections in year for the whole (i.e. if you aggregate sales, it should look at the customers that have ever bought Supplies and nver bought Machines, but then calculate the sales only for the selected years.
The last line should disregard selections in Year completely.
Hope this helps,
Stefan
Thank you Stefan!