topic Re: Using a field of multiple values in set analysis in QlikView

Using a field of multiple values in set analysis

zagzebski — Fri, 04 Jul 2014 01:11:14 GMT

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

Re: Using a field of multiple values in set analysis

Fri, 04 Jul 2014 01:18:15 GMT

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

Re: Using a field of multiple values in set analysis

zagzebski — Fri, 04 Jul 2014 01:22:32 GMT

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

DeptFilter=A

DeptFilter = B or C

DeptFilter = D, E, or F

Re: Using a field of multiple values in set analysis

Fri, 04 Jul 2014 01:22:47 GMT

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

Re: Using a field of multiple values in set analysis

zagzebski — Fri, 04 Jul 2014 01:43:07 GMT

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)

Re: Using a field of multiple values in set analysis

Fri, 04 Jul 2014 01:50:04 GMT

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

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

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

Re: Using a field of multiple values in set analysis

zagzebski — Fri, 04 Jul 2014 02:17:22 GMT

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}

Re: Using a field of multiple values in set analysis

Fri, 04 Jul 2014 02:22:05 GMT

you can try may be like

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

Re: Using a field of multiple values in set analysis

jagan — Fri, 04 Jul 2014 03:48:03 GMT

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.

Re: Using a field of multiple values in set analysis

zagzebski — Sat, 05 Jul 2014 15:56:55 GMT

Thanks I am going to try this.

Re: Using a field of multiple values in set analysis

nlagosbu — Thu, 08 Feb 2018 15:13:21 GMT

Thanks This works!