Sorting

[parvez933]

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.

[tresesco · Accepted solution]

Try like:

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

[parvez933]

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?

[tresesco · Accepted solution]

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.

[parvez933 · Accepted solution]

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!

[manoranjan_321988]

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]

Hey Manoranjan,

Thank You for your response.