10 Replies Latest reply: Sep 3, 2017 3:23 PM by Carmelle Morrison RSS

    Complex set analysis question please....

    Carmelle Morrison

      Hi there,

       

      I select several ID's and return 2 ID's for each ID selected, where Type and Class value match the selected ID.

      The below example shows the selection of ID's 1 and 4 to return:

      ID's 2 and 3 (first two records with same possible values for Type and Class as ID 1

      ID's 5 and 6 (first two records same possible values for Type and Class as ID 4

       

        

        

      Data
      IDTypeClassSales
      11A10
      21A10
      31A10
      42C20
      52C30
      62C10
      72C21
      83D34
      93D21
      104D10
      Resulting Table
      IDTypeClassSales
      21A10
      31A10
      52C30
      62C10

       

      I do this with,

      If(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}

      Aggr(Rank(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}-ID)), Type, Class, ID)) < 3,

      Sum({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}[Sales]))

       

      I would like to display the selected ID relevant to the result, such as...

        

       

      ID

       

      TypeClassSalesSelected ID
      21A101
      31A101
      52C304
      62C10  4

       

      Please can anyone offer some advice on the correct set analysis expression to use?

       

      I have also attached a small test Qlik Sense QVF.

       

      Thanks!!