Rank in a table chart with data filter

[skyline01]

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

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

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:

Person	ItemID	Price
Mark	5	$100
Bob	1	$90
Shannon	8	$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.

[sunny_talwar · Accepted solution]

Try this

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

[skyline01]

Can you please explain the execution of that statement?  I don't understand why ItemID is being compared to anything.

[sunny_talwar]

For each Item ID, the set analysis is checking if the Rank(Sum({<Status = {'Open'}>} Price)) is less than 4 or not... So basically the set modifier gives true and false.... all those ItemID which are true are shown in the table and all those which are false, fall out

[Olip]

Hey Casey,

You can take a look at this slightly different approach as well.

!

Create Set Analysis for the Sales for Status 'Open' as 'Sum({<Status = {'Open'}>} Price)'.

Then make Item ID as the first column in the sorting in the chart and after that open Item  ID in the Data Tab of the chart select Limitation >Fixed Number and Top 3