Set analysis

[polisetti]

Hi,

I have a pivot table as shown below. I need the sales in the sales column, but in the total row, i need only the sum of products having flag=x. Can anyone suggest some expression for the same.

Product	Flag	Sales
A	x	10
B	x	20
C	y	40
Total		30

If we write an expression for sales as sum(sales) where flag is minimum, the total row will consider only flag=x and I will be able to get the sales across each product as well.

Any suggestions for the expression?

Regards,

Pollisetti

[joshabbott]

=sum(<flag={'x'}>sales)

The value wouldn't show for the 'y' flag, but the others should show 10 and 20 respectively with a total of 30 for the 2nd expression column

[jerem1234 · Accepted solution]

Try something like:

if(dimensionality()=0, sum({<Flag = {'x'}>}Sales), sum(Sales))

Hope this helps!

[polisetti]

I want the value to be shown for Y as well but not to be considered in total

[polisetti]

can u suggest me the expression to calculate:

sum(flag=min(flag) sales)

[jerem1234]

Something like:

sum({<Flag={$(=min(Flag))}>}Sales)

[#7]

I can think of a lengthy workaround.

1. Create expression containing the basic sales - Expr1

= sales // Total here becomes null

2. Create second expression - Expr2

= sum(if(min(all flag) = flag,sales)) // Total here is sum of sales where flag is min

3. Create third expression which is based on first two

= if( Expr2 = 0, Expr1, Expr2) // This will give the column that you need

4. Hide the first two expressions.(option available in Presentation tab of chart properties)