Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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:
Attached a sample QVW for your testing.
That was quite challeging and fun to do .
Hope it helps,
Felipe.