Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
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
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
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
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