4 Replies Latest reply: Jan 12, 2018 11:00 AM by Alip Nath RSS

    Rank in a table chart with data filter

    Casey Marincin

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

      ItemIDPersonStatusPrice
      1BobOpen$90
      2SallyOpen$60
      3JohnClosed$30
      4BobClosed$50
      5MarkOpen$100
      6JoeOpen$40
      7AmyClosed$20
      8ShannonOpen$70
      9SallyClosed$80
      10BillOpen$10

       

      For convenience:

      data:

      Load * Inline [

      ItemID, Person, Status, Price

      1,Bob,Open,$90

      2,Sally,Open,$60

      3,John,Closed,$30

      4,Bob,Closed,$50

      5,Mark,Open,$100

      6,Joe,Open,$40

      7,Amy,Closed,$20

      8,Shannon,Open,$70

      9,Sally,Closed,$80

      10,Bill,Open,$10

      ]

      ;

       

      I need to create a table chart that returns Person, ItemID, and Price for the 3 largest Price values where Status is 'Open'.  So, from the above source, I expect to return the following table chart:

      PersonItemIDPrice
      Mark5$100
      Bob1$90
      Shannon8$70

       

      How do I create this table chart?

       

      So far, I have created a table chart with the following:

      dimensions: ItemID, Price

      measure:

           label = Person

           expression:

      If(Rank(If(Status = 'Open', Price, Null())) <= 3, Person, Null())

       

      This is not returning what I expect.  I have looked at several postings on this subject, but they all seem to aggregate by some dimension value (e.g., Person) and / or don't have a data filter.