Using a field of multiple values in set analysis

[zagzebski]

I am creating a field in my source that I can ultimately use in my set analysis.

The field is called DeptFilter.

I want to put multiple values in the DeptFilter field like:  B,F.G

Then I wan to use it in set analysis like: Sum({$<[Dept]=[DeptFilter]>}Revenue)

So in the above expression I want the set to sum the revenue only if the Dept = B, F or G

In my source file I can bring the data in anyway that makes sense:

B,F,G

'B','F','G'

('B','F','G')

So my two questions are:

1. How should I structure the 3 values in the DeptFilter field in my source

2. Then how would the expression look?

Thanks in advance,

Steve

[#2]

[Dept]=P(subfield(Dept,1), [Dept]=P(subfield(Dept,2) , .....

[zagzebski]

What if it could vary? Since the DeptFilter is tied to the a manager it could be 1 to many departments:

DeptFilter=A

or

DeptFilter = B or C

or

DeptFilter = D, E, or F

[#4]

Sum({<Dept=P(<Dept=SubField(Dept,',',1) Dept) + P(<Dept=SubField(Dept,',',2) Dept) + P(<Dept=SubField(Dept,',',3) Dept) >}Revenue)

[zagzebski]

Maybe I am not asking right I will try a different way...

Ex:

I create a field called MANAGERSDEPT. One of its values is  {'A','B','C'}

Rather than this expression

Sum({<DEPT={'A",'B',"C'}>)Revenue)

I would like to do something like:

Sum({<DEPT='MANAGERSDEPT'>}Revenue)

[#6]

Sum({<DEPT={$(=Subfield(MANAGERSDEPT,',',1)}>}Revenue)

+

Sum({<DEPT={$(=Subfield(MANAGERSDEPT,',',2)}>}Revenue)

+

Sum({<DEPT={$(=Subfield(MANAGERSDEPT,',',3)}>}Revenue)

[zagzebski]

Thanks!

Isn't there anyway to substitute the whole field like it is a variable? So it can just be something like:

DEPT=MANGERSDEPT.....which is the same as DEPT={'A'.'B','C}

[#8]

you can try may be like

sum(if (wildmatch(dept,managersdept), revenue))

[jagan]

Hi,

Try like this

create variable

vTest = chr(39) & Concat(DISTINCT FieldName, chr(39) & ',' & Chr(39) ) & chr(39)

Now in expression simply use

=Sum({<SomeOtherFieldName={$(=vTest )}>} MeasureName)

Regards,

Jagan.

[zagzebski]

Thanks I am going to try this.