Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andyrebs
Contributor III
Contributor III

Set Analysis: select data, exclude data

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

5 Replies
Miguel_Angel_Baeyens

Hi,

That should be something like

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

Hope that helps.

Miguel

swuehl
MVP
MVP

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.

andyrebs
Contributor III
Contributor III
Author

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?

swuehl
MVP
MVP

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

andyrebs
Contributor III
Contributor III
Author

Thank you Stefan!