1 Reply Latest reply: Oct 20, 2017 9:31 AM by Felip Drechsler RSS

    Select by multiple dimension values

    Theis Høvring

      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?

        • Re: Select by multiple dimension values
          Felip Drechsler

          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.