topic Set Analysis: select data, exclude data in QlikView

Set Analysis: select data, exclude data

andyrebs — Fri, 15 Jun 2012 12:36:11 GMT

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

Re: Set Analysis: select data, exclude data

Miguel_Angel_Baeyens — Fri, 15 Jun 2012 12:41:59 GMT

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

swuehl — Fri, 15 Jun 2012 12:46:40 GMT

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.

Re: Set Analysis: select data, exclude data

andyrebs — Fri, 15 Jun 2012 13:22:57 GMT

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 — Fri, 15 Jun 2012 13:46:26 GMT

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

Re: Set Analysis: select data, exclude data

andyrebs — Fri, 15 Jun 2012 14:35:51 GMT

Thank you Stefan!