5 Replies Latest reply: Jun 15, 2012 10:35 AM by Andrea Rebora RSS

Set Analysis: select data, exclude data

Andrea Rebora

Hello!

I have these data:

 

N°OrderProductProductPriceProduct Type
CustomerID
11111Pen1,00Supplies1
11111Rubber4,00Supplies1
22222Paper3,00Supplies2
33333Paper3,00Machine1
44444Bag15,00Machine3
55555Pencil2,00Supplies4
55555Pen1,00Supplies4

 

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

  • Re: Set Analysis: select data, exclude data
    Miguel Angel Baeyens

    Hi,

     

    That should be something like

     

    Count({< [Product Type] = {'Supplies'} > - < [Product Type] = {'Machine'} >} CustomerID)
    

     

    Hope that helps.

     

    Miguel

  • Re: Set Analysis: select data, exclude data
    Andrea Rebora

     

    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?

    • Re: Set Analysis: select data, exclude data
      swuehl

      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