topic Rank in a table chart with data filter in App Development

Rank in a table chart with data filter

skyline01 — Thu, 11 Jan 2018 20:41:15 GMT

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.

Re: Rank in a table chart with data filter

sunny_talwar — Thu, 11 Jan 2018 20:57:01 GMT

Try this

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

Re: Rank in a table chart with data filter

skyline01 — Thu, 11 Jan 2018 22:13:37 GMT

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

Re: Rank in a table chart with data filter

sunny_talwar — Fri, 12 Jan 2018 00:53:59 GMT

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

Re: Rank in a table chart with data filter

AlipNath — Fri, 12 Jan 2018 15:56:18 GMT

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