Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

parvez933
New Contributor III

Sorting

Hello Everyone!

Please help me with the below scenario

load * inline [
Products, Sales
P1, 123
P2, 234
P3, 345
P4, 456
P5, 567
P6, 678
P7, 789
P8, 891
P9, 911
P10, 124
];

Question:
With in a straight table, display the products whose rank(sales) ranges between 3 to 7 by using
1. Set Analysis with p()
2. By using the rank() in the if loop or other

Note:
The products should be from rank3 to rank7.

I tried by using the rownum() in the if loop the data is sorting on basis of rownum(). But I need sort the data on the basis of rank of sales. Any suggestions please.

3 Solutions

Accepted Solutions
MVP
MVP

Re: Sorting

Try like:

Sum({<Products={"=Rank(Sum(Sales))>2 and Rank(Sum(Sales))<8"}>}Sales)

Capture.JPG

View solution in original post

MVP
MVP

Re: Sorting

p() and e() are elements of set analysis. If you know it when and where to use it, you could use them in aggregation functions (here sum(); both inner and outer). If you are not sure where, how.. I would request to explain more about your business case - to understand - even if that is at all required or not. If yes, we may give a try to show and help you how.

View solution in original post

parvez933
New Contributor III

Re: Sorting

Hey MVP,
I just refined your logic
=Sum({<Products=p({<Products={"=rank(sum(Sales))>2 and rank(Sum(Sales))<8"}>})>}Sales)

and it is showing the required output.
Thank you!

View solution in original post

6 Replies
MVP
MVP

Re: Sorting

Try like:

Sum({<Products={"=Rank(Sum(Sales))>2 and Rank(Sum(Sales))<8"}>}Sales)

Capture.JPG

View solution in original post

parvez933
New Contributor III

Re: Sorting

Hey MVP,
Thank you for the prompt reply. I tried the way you suggested it works. I was not sure using rank() in the set analysis.
Can you please help me how define the same logic using the p() or e() in the set analysis?
MVP
MVP

Re: Sorting

p() and e() are elements of set analysis. If you know it when and where to use it, you could use them in aggregation functions (here sum(); both inner and outer). If you are not sure where, how.. I would request to explain more about your business case - to understand - even if that is at all required or not. If yes, we may give a try to show and help you how.

View solution in original post

parvez933
New Contributor III

Re: Sorting

Hey MVP,
I just refined your logic
=Sum({<Products=p({<Products={"=rank(sum(Sales))>2 and rank(Sum(Sales))<8"}>})>}Sales)

and it is showing the required output.
Thank you!

View solution in original post

Highlighted
manoranjan_321988
New Contributor

Re: Sorting

try this expression instead of going set analysis in expression tab

i tried this one its working and paste the below expression in sort tab also

 

Expression tab: 

=if(rank(Sales)>=3 and rank(Sales)<=7,sum(Sales))

 

sort tab

=if(rank(Sales)>=3 and rank(Sales)<=7,rank(Sales))

parvez933
New Contributor III

Re: Sorting

Hey Manoranjan,

Thank You for your response.