4 Replies Latest reply: Jan 26, 2018 8:49 PM by Andy Cosgrove RSS

    Ranking by 1 measure while showing the data for other measures

    Andy Cosgrove

      Hi I'm using the following to rank a table based on 1 measure and limiting the  rows to 25.

       

      =aggr(if(rank(-Q_DCIPU,Q_DCIPU )<=25, Q_DCIPU ),Q_DCIPU )

       

      I want to include more data columns while maintaining the same 25 rows as follows based on the same ranking

      AssetQ_DCIPUSDWPUINDEX
      A11500.50
      B1.50100.01
      C2.2560.60
      D2.2670

      0.40

      E5.99990.06

       

      However, when I try something like

      =aggr(if(rank(-Q_DCIPU,Q_DCIPU )<=25, SDWPU ), SDWPU )

      and

      =aggr(if(rank(-Q_DCIPU,Q_DCIPU )<=25, INDEX ), INDEX )


      I get a result similar to the following and more rows due to the ranking function as each measure adds another row until the 25 rank values are showing for each measure which is what I don't want

      AssetQ_DCIPUSDWPUINDEX
      A1--
      B1.50100.01
      C2.256-
      D2.26--
      E5.99-0.06

       

      hope you can help and appreciate it if you can