topic Re: Set Analysis with AND condition in QlikView

Set Analysis with AND condition

mhassinger — Tue, 03 Nov 2015 19:23:13 GMT

I'm having a hard time structuring my set analysis to make this work. Let's say i have a simple table like:

Customer	Product	Qty
Smith	Widget	5
Smith	Cog	10
Jones	Widget	5

I want to return a result of all customers who bought 2 or more widgets AND 4 or more cogs. Smith is the only customer that would meet that criteria.

My expression looks like this:

SUM({<Qty={">=2"},Product={'Widget'}> * <Qty={">=4"},Product={'Cog'}>} Qty)

The problem is that this returns zero results, because there are no records that meet both criteria (only one product per record). I've attached a sample QVW.

Any ideas?

Re: Set Analysis with AND condition

varunreddy — Tue, 03 Nov 2015 19:36:21 GMT

Hi,

Try this:

aggr(SUM({<Qty={">=2"},Product={'Widget'}> * <Qty={">=4"},Product={'Cog'}>} Qty), Customer).

Let me know if you have any issues

Regards,

Varun

Re: Set Analysis with AND condition

sunny_talwar — Tue, 03 Nov 2015 19:42:03 GMT

May be this:

{<Customer = p({<Qty = {'>=2'}, Product = {'Widget'}>}) * p({<Qty = {'>=4'}, Product = {'Cog'}>})>}

Re: Set Analysis with AND condition

maxgro — Tue, 03 Nov 2015 19:44:03 GMT

sum( {$ <Customer={"=sum({$<Product={Widget}>}Qty)>=2"}*{"=sum({$<Product={Cog}>}Qty)>=4"}>} Qty)

Re: Set Analysis with AND condition

rwunderlich — Tue, 03 Nov 2015 19:52:37 GMT

I think you've got a couple of good answers there but I wanted to offer up a Set Analysis tip. Your answer was in your question: "return a result of all customers.." therefore your field selector is Customer.

-Rob