Rank in table

skyline01 — Fri, 12 Jan 2018 16:08:55 GMT

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?

Re: Rank in table

sunny_talwar — Fri, 12 Jan 2018 16:18:20 GMT

This seems to work for me

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

Re: Rank in table

ali_hijazi — Fri, 12 Jan 2018 16:20:08 GMT

Hello

in't this what you want?

check attached file

topic Rank in table in App Development

Rank in table

Re: Rank in table

Re: Rank in table