Select by multiple dimension values

Anonymous — Thu, 19 Oct 2017 10:39:28 GMT

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.

Dim1	Dim2
A	X
A	Y
B	X
B	Z
C	X
C	Y
C	Z

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?

Re: Select by multiple dimension values

felipedl — Fri, 20 Oct 2017 13:31:40 GMT

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

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:

Attached a sample QVW for your testing.

That was quite challeging and fun to do .

Hope it helps,

Felipe.

topic Select by multiple dimension values in App Development

Select by multiple dimension values

Re: Select by multiple dimension values