Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

skyline01
New Contributor

Rank in a table chart with data filter

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

ItemIDPersonStatusPrice
1BobOpen$90
2SallyOpen$60
3JohnClosed$30
4BobClosed$50
5MarkOpen$100
6JoeOpen$40
7AmyClosed$20
8ShannonOpen$70
9SallyClosed$80
10BillOpen$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:

PersonItemIDPrice
Mark5$100
Bob1$90
Shannon8$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.

Tags (1)
1 Solution

Accepted Solutions

Re: Rank in a table chart with data filter

Try this

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


Capture.PNG

4 Replies

Re: Rank in a table chart with data filter

Try this

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


Capture.PNG

skyline01
New Contributor

Re: Rank in a table chart with data filter

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

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

alip_nath
Contributor

Re: Rank in a table chart with data filter

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

Chart Top3 Screenshot.JPG

Community Browser