Set Analysis with AND condition

[mhassinger]

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?

[varunreddy]

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

[sunny_talwar · Accepted solution]

May be this:

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

[maxgro]

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

[rwunderlich]

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