2 Replies Latest reply: Jan 4, 2012 6:23 AM by user5674 RSS

    Pivot table - Duplicated values - Top 5 by Dimension values

      Hi,

       

      I want to make a pivot table with a top 5 by dimension values.

       

      I used function aggr and rank with an expression like aggr(if(rank(myField)<6, Id),Mydimension)

      but As you can see in attached QlikView file, I have more than 5 values by dimension value A.

       

      My fourth column isn't a sum but a max value (I don't want use sum here).

       

      It seems it's because duplicate values are ranked as the same rank value.

      I have this:

      CategoryRnkTop 5 Id ranked by Amount Gap group by CategoryAmount Gap
      A18625
      2-6465
      665
      365
      965
      1065
      B1271
      2560
      3450
      4130
      5320
      C1371
      2265
      3450
      4130
      D1984
      2365
      3260
      4150
      5420


      And I want this:

      CategoryTop 5 Id ranked by Amount Gap group by CategoryAmount Gap
      A8625
      1065
      965
      665
      465
      B271
      560
      450
      130
      320
      C371
      265
      450
      130
      D984
      365
      260
      150
      420

       

      Could you help me?

       

      Thanks.