Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
[Dept]=P(subfield(Dept,1), [Dept]=P(subfield(Dept,2) , .....
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
Sum({<Dept=P(<Dept=SubField(Dept,',',1) Dept) + P(<Dept=SubField(Dept,',',2) Dept) + P(<Dept=SubField(Dept,',',3) Dept) >}Revenue)
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)
Sum({<DEPT={$(=Subfield(MANAGERSDEPT,',',1)}>}Revenue)
+
Sum({<DEPT={$(=Subfield(MANAGERSDEPT,',',2)}>}Revenue)
+
Sum({<DEPT={$(=Subfield(MANAGERSDEPT,',',3)}>}Revenue)
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}
you can try may be like
sum(if (wildmatch(dept,managersdept), revenue))
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.
Thanks I am going to try this.