Rank in table

[skyline01]

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:

Person	Company - Description	Region	Price
Mark	Company2 - z	3	$100
Bob	Company1 - x	1	$90
Shannon	Company2 - z	4	$70
Sally	Company2 - y	1	$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?

[sunny_talwar · Accepted solution]

This seems to work for me

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

[ali_hijazi]

Hello

in't this what you want?

check attached file