2 Replies Latest reply: Jan 12, 2018 11:20 AM by Ali Hijazi RSS

    Rank in table

    Casey Marincin

      I am working in Qlik Sense 3.2.  I have the following data table:

      data:

      Load * Inline [

      ItemID,Person,Company,Description,Region,Status,Price

      a,Bob,Company1,x,1,Open,$90

      b,Sally,Company2,y,1,Open,$60

      c,John,Company1,x,2,Closed,$30

      d,Bob,Company3,y,2,Closed,$50

      e,Mark,Company2,z,3,Open,$100

      f,Joe,Company1,y,3,Open,$40

      g,Amy,Company4,z,3,Closed,$20

      h,Shannon,Company2,z,4,Open,$70

      i,Sally,Company4,x,4,Closed,$80

      j,Bill,Company2,y,4,Open,$10

      ]

      ;

       

      I want to create a table visualization that gives Person, Company & ' - ' & Description, Region, and Price for the top 5 Price values.  So, I expect the following output:

      PersonCompany - DescriptionRegionPrice
      MarkCompany2 - z3$100
      BobCompany1 - x1$90
      ShannonCompany2 - z4$70
      SallyCompany2 - y1$60

       

      I am trying to achieve this solely in the visualization (i.e., without enhancements to the load script).  How can I do so?

       

      My table definition so far is:

      dimensions: Person, Company & ' - ' & Description, Region

      measure:

      =Sum(

      {

      <ItemID= {"=Rank(Sum({<[Status= {'Open'}>} Price)) <= 5"}>

      }

      Price

      )

       

      sort in descending order by the measure

       

      However, this does not achieve the desired result.  It shows 1 row, with 0 for the measure, and nulls for the dimension values.  What do I need to do to fix it?