Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
zagzebski
Contributor

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

Tags (2)
10 Replies
Not applicable

Re: Using a field of multiple values in set analysis

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

zagzebski
Contributor

Re: Using a field of multiple values in set analysis

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

Re: Using a field of multiple values in set analysis

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

zagzebski
Contributor

Re: Using a field of multiple values in set analysis

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

Re: Using a field of multiple values in set analysis

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

+

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

+

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

zagzebski
Contributor

Re: Using a field of multiple values in set analysis

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

Re: Using a field of multiple values in set analysis

you can try may be like

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

Highlighted
MVP
MVP

Re: Using a field of multiple values in set analysis

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
Contributor

Re: Using a field of multiple values in set analysis

Thanks I am going to try this.

Community Browser