Set Analysis in Comparative Analysis

[#1]

Hi,

i want to compare the amount of sales between one set (composed by only invoice that contain at the same time the list of products selected in group1) and another (composed by only invoice that contain at the same time the list of products selected in group2). See attachment.

	NO_Invoice, Product, Retail, Sales, Qty
	1, a, aa, 10, 1
	1, b, aa, 20, 1
	1, c, aa, 30, 1
	2, a, aa, 10, 1
	2, b, aa, 20, 1
	3, e, bb, 40, 1
	3, f, bb, 50, 1
	3, b, bb, 20, 1
	3, c, bb, 60, 2

For example i want create a chart with dimension Reatil and measure sum(sales)

if i select in group1: A and B and in group2 B and E

the result of sum sales i expected is this

group1: aa=60 bb=0

group2: aa=0 bb=60

unfortunately

the result is this

group1: aa=60 bb=20

group2: aa=40 bb=60

because my expression goes in OR

sum({$<Product= [group1]::Product>}Sales)

sum({$<Product= [group2]::Product>}Sales)

contrariwise i want an AND between the product...

[Anonymous]

Paolo,

Can you explain the logic of the expectation?  For example - why should be bb=0 for group1?  In your data, the second last line is
3, b, bb, 20, 1

Regards,

Michael

[#3]

Hi Michael,

I want to add only products (that are present in group1) that appear on the same invoice. This means that the retail bb has never issued a bill that contains both the product a and product b. It should be bb=0 for group1 because in invoice 3 don't appear a sell of product a (group1 is composed: a AND b). I hope I made myself clear.

Thx

[#4]

Hi,

i resolved partially my problem with this expression:

if (aggr(count(NO_Invoice),NO_Invoice)=GetSelectedCount(Product),aggr(sum(Sales),Retail),)

now i want create 2 indipendent groups with set analysis

if (aggr(count({[group1]}NO_Invoice),NO_Invoice)=GetSelectedCount(Product),aggr(sum({[group1]}Sales),Retail),)

if (aggr(count({[group2]}NO_Invoice),NO_Invoice)=GetSelectedCount(Product),aggr(sum({[group2]}Sales),Retail),)

but i've an error on the creation of the chart: No data to display... see new attachment

[Anonymous]

I got it, the part I missed was "on the same invoice".

See attached, second chart.  Expressions:

sum({$<Product= [group1]::Product>} if(aggr(nodistinct count({[group1]} distinct Product),NO_Invoice)>1, Sales))

sum({$<Product= [group2]::Product>} if(aggr(nodistinct count({[group2]} distinct Product),NO_Invoice)>1, Sales))

Regards,

Michael

[#6]

Hi Michael!

Thx so much for your tip! I modify your expression like this:

sum({$<Product= [group1]::Product>} if(aggr(nodistinct count({[group1]} distinct Product),NO_Invoice)=Variable2, Sales))

where Variable2 is defined as count({[group1]} distinct Product)

because with >1 don't work dinamically. In fact in your qvw if i select 3 Product the chart also show those invoice with 2 product...

I thank you again for your valuable suggestion