topic Re: Sorting in QlikView

Sorting

parvez933 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Sorting

tresB — Fri, 30 Nov 2018 09:36:15 GMT

Try like:

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

Re: Sorting

parvez933 — Fri, 30 Nov 2018 09:54:56 GMT

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?

Re: Sorting

tresB — Fri, 30 Nov 2018 10:05:26 GMT

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.

Re: Sorting

parvez933 — Fri, 30 Nov 2018 10:08:03 GMT

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!

Re: Sorting

manoranjan_321988 — Fri, 30 Nov 2018 14:09:42 GMT

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))

Re: Sorting

parvez933 — Sat, 01 Dec 2018 03:53:32 GMT

Hey Manoranjan,

Thank You for your response.