Set Analysis with multiple selections

[#1]

I have defined a set analysis as below in a Pivot Chart. It works fine when I have only one selection made for FIELD2 as I was using getfieldselections. FIELD2 is actually one of the dimensions. When I make multiple selections the output of getfieldselections(FIELD2) will be several values separated by comma like A,B,C

=Sum({$<FIELD1={'$(=getfieldselections(FIELD2))' }>} FIELD3)

It there a way to use set analysis to wildmatch the selections made for FIELD2 and get the sum?

[sunny_talwar]

Try this may be:

=Sum({$<FIELD1={"$(=Chr(34) & GetFieldselections(FIELD2, Chr(34) & ', ' & Chr(34)) & Chr(34))"}>} FIELD3)

=Sum({$<FIELD1={"$(=Chr(39) & GetFieldselections(FIELD2, Chr(39) & ', ' & Chr(39)) & Chr(39))"}>} FIELD3)

[#3]

Thanks for the response sunny, I have tried butt this doesnt work.

We are basically equating one value to multiple values

For example:

if FIELD1 is A and values selected for FIELD2 is A,B,C

Then the equation would be something like this.

A= 'A','B','C'  

Which is not a true statement.

[sunny_talwar]

Then I am not sure why you are trying to do that? What is the requirement here?

[swuehl]

I think the double quotes need to be removed from the set modifier.

=Sum({$<FIELD1={$(=Chr(39) & GetFieldselections(FIELD2, Chr(39) & ', ' & Chr(39)) & Chr(39))}>} FIELD3)

If you select A,B and C in FIELD2, this dollar sign expansion should evaluate to

=Sum({$<FIELD1={'A','B','C'}>} FIELD3)

I assume that is what you want? If not, please elaborate your requirement and post some sample field values for FIELD1 and FIELD2.

[swuehl]

Another option if you want to modify FIELD1 set, applying the FIELD2 set is

=Sum({$<FIELD1= FIELD2 >} FIELD3)

[#7]

Here is the requirement.

I have a pivot table with dimensions

FIELD0 and FIELD2

Expressions:

=Sum({$<FIELD1={'$(=getfieldselections(FIELD2))' }>} FIELD3)  --> Expression 1

=Sum({$<FIELD4={'$(=getfieldselections(FIELD2))' }>} FIELD5)  --> Expression 2

If I have one selection for FIELD2 (for example A)  the output would be

FIELD0	FIELD2	EXP1	EXP2
123	A	30	20

I am able to achieve the above with this formula

=Sum({$<FIELD1={'$(=getfieldselections(FIELD2))' }>} FIELD3)

but When I select more then one value for FIELD2 (for example A,B) I want my output to be

FIELD0	FIELD2	EXP1	EXP2
123	A	30	20
123	B	25	28
142	B	34	54

but the below formula doesnt work for me 

=Sum({$<FIELD1={$(=Chr(39) & GetFieldselections(FIELD2, Chr(39) & ', ' & Chr(39)) & Chr(39))}>} FIELD3)

because it is adding up everything and giving me result like below.

FIELD0	FIELD2	EXP1	EXP2
123	A	89	102
123	B	89	102
142	B	89	102

Was I clear?

[sunny_talwar]

What data do you have in FIELD1? How is that is linked to FIELD2?

[swuehl]

Looks like you want Field1 to only consider the current dimension value of FIELD2.

Set analysis can't do this, it's not evaluated per dimension like.

You would probably need SUM(IF(...)) for that.

[#10]

The data is something similar to this.

FIELD0	FIELD1	FIELD4
123	A	B
123	A	C
123	Z	A
123	B	C
123	B	D
142	A	B
142	B	C
142	X	A

FIELD2 lookup table which has distinct value of FIELD1 and FIELD4

FIELD2

A

B

C

D

X

Z

For this data if I select FIELD2 as A I should get

FIELD0	FIELD2	EXP1	EXP2
123	A	2	1
142	A	1	1

If I select two values that is A,B. I want the output to be

FIELD0	FIELD2	EXP1	EXP2
123	A	2	1
142	A	1	1
123	B	2	1
142	B	1	1