Qlik Community

Qlik Sense App Development

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

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
skyline01
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
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

Highlighted
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