9 Replies Latest reply: Sep 1, 2017 11:15 AM by Sunny Talwar RSS

    Complex set analysis question please....

    Craig Murray

      Hi there,

       

      I would like to 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 have tried the following expression, but am only getting the two selected results (ID 1 and 4), image attached...

      IF(RANK(AGGR(Sum({1<ID = ,Type = (P(Type)),Class = (P([Class]))>}[Sales]),ID))<=2,

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

       

      Please can someone advise as to the correct expression?

       

      I have also attached a small test Qlik Sense QVF.

       

      Thank you!!!