Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Set Analysis in Comparative Analysis

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

5 Replies
Highlighted
mov
Esteemed Contributor III

Re: Set Analysis in Comparative Analysis

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

Not applicable

Re: Set Analysis in Comparative Analysis

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


Not applicable

Re: Re: Set Analysis in Comparative Analysis

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

mov
Esteemed Contributor III

Re: Re: Re: Set Analysis in Comparative Analysis

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

Not applicable

Re: Set Analysis in Comparative Analysis

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