topic Re: Set Analysis with multiple selections in QlikView

Set Analysis with multiple selections

Fri, 30 Oct 2015 04:23:15 GMT

I have defined a set analysis as below in a Pivot Chart. It works fine when I have only one selection made for FIELD2 as I was using getfieldselections. FIELD2 is actually one of the dimensions. When I make multiple selections the output of getfieldselections(FIELD2) will be several values separated by comma like A,B,C

=Sum({$<FIELD1={'$(=getfieldselections(FIELD2))' }>} FIELD3)

It there a way to use set analysis to wildmatch the selections made for FIELD2 and get the sum?

Re: Set Analysis with multiple selections

sunny_talwar — Fri, 30 Oct 2015 15:30:18 GMT

Try this may be:

=Sum({$<FIELD1={"$(=Chr(34) & GetFieldselections(FIELD2, Chr(34) & ', ' & Chr(34)) & Chr(34))"}>} FIELD3)

=Sum({$<FIELD1={"$(=Chr(39) & GetFieldselections(FIELD2, Chr(39) & ', ' & Chr(39)) & Chr(39))"}>} FIELD3)

Re: Set Analysis with multiple selections

Fri, 30 Oct 2015 18:24:45 GMT

Thanks for the response sunny, I have tried butt this doesnt work.

We are basically equating one value to multiple values

For example:

if FIELD1 is A and values selected for FIELD2 is A,B,C

Then the equation would be something like this.

A= 'A','B','C'

Which is not a true statement.

Re: Set Analysis with multiple selections

sunny_talwar — Fri, 30 Oct 2015 18:48:28 GMT

Then I am not sure why you are trying to do that? What is the requirement here?

Re: Set Analysis with multiple selections

swuehl — Fri, 30 Oct 2015 18:51:59 GMT

I think the double quotes need to be removed from the set modifier.

=Sum({$<FIELD1={$(=Chr(39) & GetFieldselections(FIELD2, Chr(39) & ', ' & Chr(39)) & Chr(39))}>} FIELD3)

If you select A,B and C in FIELD2, this dollar sign expansion should evaluate to

=Sum({$<FIELD1={'A','B','C'}>} FIELD3)

I assume that is what you want? If not, please elaborate your requirement and post some sample field values for FIELD1 and FIELD2.

Re: Set Analysis with multiple selections

swuehl — Fri, 30 Oct 2015 19:06:19 GMT

Another option if you want to modify FIELD1 set, applying the FIELD2 set is

=Sum({$<FIELD1= FIELD2 >} FIELD3)

Re: Set Analysis with multiple selections

Fri, 30 Oct 2015 19:24:41 GMT

Here is the requirement.

I have a pivot table with dimensions

FIELD0 and FIELD2

Expressions:

=Sum({$<FIELD1={'$(=getfieldselections(FIELD2))' }>} FIELD3) --> Expression 1

=Sum({$<FIELD4={'$(=getfieldselections(FIELD2))' }>} FIELD5) --> Expression 2

If I have one selection for FIELD2 (for example A) the output would be

FIELD0	FIELD2	EXP1	EXP2
123	A	30	20

I am able to achieve the above with this formula

=Sum({$<FIELD1={'$(=getfieldselections(FIELD2))' }>} FIELD3)

but When I select more then one value for FIELD2 (for example A,B) I want my output to be

FIELD0	FIELD2	EXP1	EXP2
123	A	30	20
123	B	25	28
142	B	34	54

but the below formula doesnt work for me

=Sum({$<FIELD1={$(=Chr(39) & GetFieldselections(FIELD2, Chr(39) & ', ' & Chr(39)) & Chr(39))}>} FIELD3)

because it is adding up everything and giving me result like below.

FIELD0	FIELD2	EXP1	EXP2
123	A	89	102
123	B	89	102
142	B	89	102

Was I clear?

Re: Set Analysis with multiple selections

sunny_talwar — Fri, 30 Oct 2015 19:34:30 GMT

What data do you have in FIELD1? How is that is linked to FIELD2?

Re: Set Analysis with multiple selections

swuehl — Fri, 30 Oct 2015 19:42:28 GMT

Looks like you want Field1 to only consider the current dimension value of FIELD2.

Set analysis can't do this, it's not evaluated per dimension like.

You would probably need SUM(IF(...)) for that.

Re: Set Analysis with multiple selections

Fri, 30 Oct 2015 19:46:35 GMT

The data is something similar to this.

FIELD0	FIELD1	FIELD4
123	A	B
123	A	C
123	Z	A
123	B	C
123	B	D
142	A	B
142	B	C
142	X	A

FIELD2 lookup table which has distinct value of FIELD1 and FIELD4

FIELD2

For this data if I select FIELD2 as A I should get

FIELD0	FIELD2	EXP1	EXP2
123	A	2	1
142	A	1	1

If I select two values that is A,B. I want the output to be

FIELD0	FIELD2	EXP1	EXP2
123	A	2	1
142	A	1	1
123	B	2	1
142	B	1	1

Re: Set Analysis with multiple selections

sunny_talwar — Fri, 30 Oct 2015 20:25:46 GMT

I am not sure I gather completely. If you are making a selection in Field2, then you will only see what you have in Field2 (unless you have a restricting set analysis)

This is what you want?

Re: Set Analysis with multiple selections

Fri, 30 Oct 2015 20:29:21 GMT

You are missing the expressions. Which is like count of appearances.

Re: Set Analysis with multiple selections

Fri, 30 Oct 2015 20:35:51 GMT

Do you think this can be achieved using SUM(IF(...)) ?

Re: Set Analysis with multiple selections

swuehl — Fri, 30 Oct 2015 20:48:41 GMT

Yes...

=count(If(FIELD2=FIELD1,FIELD1))

Re: Set Analysis with multiple selections

Fri, 30 Oct 2015 21:09:56 GMT

This could be done in the this way but I have one more expression which involves a filter in the set analysis.

I think I will have to move the 3rd expression to different table.

=Sum({$<FIELD1={'$(=getfieldselections(FIELD2))' },FIELD5={0}>} FIELD6) --> Third expresion

Thanks for your help.

Re: Set Analysis with multiple selections

Fri, 30 Oct 2015 21:51:54 GMT

Please try this:

=Sum({$< $(='FIELD1 = {' & getfieldselections(FIELD2) & '}') >} FIELD3)

If FIELD2 values contain spaces then you need to concatenate them using single quotes.

=Sum({$< $(='FIELD1 = {' & Chr(39) & getfieldselections(FIELD2, Chr(39) & ',' & Chr(39)) & Chr(39) & '}') >} FIELD3)

Best regards,

Daniel