Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
zagzebski
Creator
Creator

Using a field of multiple values in set analysis

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

10 Replies
Not applicable

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

zagzebski
Creator
Creator
Author

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

Not applicable

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

zagzebski
Creator
Creator
Author

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)

Not applicable

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

+

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

+

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

zagzebski
Creator
Creator
Author

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}

Not applicable

you can try may be like

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

jagan
Luminary Alumni
Luminary Alumni

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
Creator
Creator
Author

Thanks I am going to try this.