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?
