topic Re: Set Analysis in Comparative Analysis in QlikView

Set Analysis in Comparative Analysis

Tue, 12 Nov 2013 16:09:26 GMT

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...

Re: Set Analysis in Comparative Analysis

Anonymous — Tue, 12 Nov 2013 17:47:32 GMT

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

Re: Set Analysis in Comparative Analysis

Wed, 13 Nov 2013 07:51:00 GMT

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

Re: Re: Set Analysis in Comparative Analysis

Wed, 13 Nov 2013 13:41:32 GMT

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

Re: Re: Re: Set Analysis in Comparative Analysis

Anonymous — Wed, 13 Nov 2013 16:41:52 GMT

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

Re: Set Analysis in Comparative Analysis

Fri, 15 Nov 2013 10:57:04 GMT

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