Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

theishovring
New Contributor

Select by multiple dimension values

Hi,

I'm trying to create a selection where there's an "AND" and not "OR" between the values. I will try to explain with an example.

In the table  below I have 3 outcomes of Dim1, A, B and C. I want to be able to create a selection of Dim1 by choosing values in Dim2. If I choose X, I will be shown Dim1 = A, B, C. If I choose X and Y I will only be shown A and C, since B don't have both X and Y. If I chose X, Y and Z I will only be shown C, since neither A or B have all 3 values.

Dim1Dim2
AX
AY
BX
BZ
CX
CY
CZ

This is a simple example. I have a Dim1 with 1.000 diferent values and a Dim2 with 20.000 different values, where usually 5-10 exist for each Dim1. That means transposing Dim2 wont be an option.

Is there a way to make a Dim1 selection using a filterpane based on Dim2 values with a "AND" criteria?

1 Reply
felipedl
Valued Contributor III

Re: Select by multiple dimension values

Hi Theis,

Took me a while, but to do that, you'll need to do a dynamic set analysis, since you have to address multiple set intersections.

The following code generates a dynamic intersection set analysis, based on the selections made on Dim2 field:

// Inline table

x:

load * Inline

[

Dim1, Dim2

A, X

A, Y

B, X

B, Z

C ,X

C ,Y

C ,Z

];

// Ordering the table by Dim2

NoConcatenate

data:

Load

*

Resident x

order by Dim2;

// Getting distinct Dim2 values

teste:

Load

Distinct Dim2

Resident data;

// Creating an index so that it is possible to check which values must be displayed

Index:

load

Dim2,

RecNo() as Index,

Dim2 as OrderDim2

Resident teste

order by Dim2 asc;

drop tables teste,x;

// simple counter

let i = 1;

// for each value of Dim2 field, create individual set analysis and multiplication (intersection) value *

for each Value in FieldValueList('OrderDim2')

let teste = Lookup('Index','Dim2','$(Value)','Index');

set vSetPart$(i) = if(index(concat(Index,','),'$(teste)'),'<Dim1=P({<Dim2={"$(Value)"}>}Dim1)>','');

set vTest$(i) = '';

set x$(i) = '=$(vSetPart$(i))';

set asterisk$(i) = =if(index(concat(Index,','),'$(teste)') and GetPossibleCount(Index)>1 and MaxString(Index)<>'$(teste)','*','');

i = i+1;

next;

The above code gives me the following:

Sample.png

Attached a sample QVW for your testing.

That was quite challeging and fun to do .

Hope it helps,

Felipe.