Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MK_QSL
MVP
MVP

Same Rank after Selection

I need urgent help on rank analysis.

Check enclosed file for data and requirements.

What I need is already explained in file.

I need urgent help on rank analysis.

Check file for data and requirements.

What I need is already explained in file.

Thanks

MK

Data is something like below

Data:
Load * Inline
[  
Year,   Customer, Sales
2013,   A,  120
2013,   B,  200
2013,   C,  250
2013,   D,  245
2013,   E,  175
2013,   F,  100
2014,   A,  175
2014,   B,  225
2014,   C,  240
2014,   D,  300
2014,   E,  200
2014,   F,  135
2015,   A,  200
2015,   B,  160
2015,   C,  180
2015,   D,  240
2015,   E,  200
2015,   F,  160
2016,   A,  300
2016,   B,  225
2016,   C,  145
2016,   D,  200
2016,   E,  180
2016,   F,  175
]
;

I have created two tables

Capture.JPG

When I select any customer, I need rank as below.

Say I select Customer A


Rank by Year table should show me
2013 Rank 5
2014 Rank 5
2015 Rank 2-3
2016 Rank 1

Sales Analysis table should show as

        2013 2014    2015    2016
A       5          5          2-3       1

1 Solution

Accepted Solutions
sunny_talwar

For rank, try this:

Aggr(Rank(Sum({1}Sales)), Year, Customer)

Capture.PNG

View solution in original post

9 Replies
sunny_talwar

For rank, try this:

Aggr(Rank(Sum({1}Sales)), Year, Customer)

Capture.PNG

MK_QSL
MVP
MVP
Author

Thanks Sunny,

But Sales Analysis table is not showing proper rank.

NOTE: Sorry for my earlier reply. Looks like it's working. Let me check and will come back to you.

sunny_talwar

I did not add the expression to that one. Now I did and have updated the image as well

sunny_talwar

You can also try this:

Alt(Aggr(Rank(Sum({1}Sales)), Year, Customer), Rank(Sum(Sales)))

Will show regular rank when Customer isn't selected and will show Customer specific rank when a single customer is selected.

MK_QSL
MVP
MVP
Author

after little modification for allowing other filters, this working perfectly.

Thanks..!

MK_QSL
MVP
MVP
Author

stalwar1

What if I want below Rank by Year table for Customer A without selecting customer A.

Capture.PNG

i.e. Rank by Year table should show us Rank of Customer A for each Year compare to all other customers for the same year.


sunny_talwar

May be this?

Only({<Customer = {'A'}>}Aggr(Rank(Sum({1}Sales)), Year, Customer))

Capture.PNG


MK_QSL
MVP
MVP
Author

I also tried below and about to write...thnx...

My mind start functioning again !! LOL

Only({<Customer={'A'}>} Aggr(Rank(Sum({<Customer>}Sales),1,1), Year, Customer))

sunny_talwar

Yay!!!!