Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank

Need assistance in terms to develop a pivot table with Rank function:

Requirement:

We have a map where we are plotting Rank City wise based on Score.

If a particular user click on a specific city, a pivot table will be shown with the rank of the selected city in comparision to the rest of cities

Data is as follows:

   

YearCityLevel1Level2Level3Level4Score
2015ABCEnbPE & SEPEQ184.62
2015ABCEnbPE & SEPEQ290.35
2015ABCEnbPE & SEPEQ380.00
2015ABCEnbPE & SEEGQ458.82
2015ABCEnbPE & SEEGQ561.21
2015ABCEnbPE & SEEGQ683.07
2015PQREnbPE & SEPEQ134.62
2015PQREnbPE & SEPEQ235.74
2015PQREnbPE & SEPEQ390.00
2015PQREnbPE & SEEGQ460.00
2015PQREnbPE & SEEGQ580.00
2015PQREnbPE & SEEGQ665.00

 

 

Complete Pivot table output:

   

CityLevel1Level2Level3Level4ScoreRank
ABCEnb 76.341
PE & SE 76.341
PE 84.991
Q184.621
Q290.351
Q380.002
EG 67.702
Q458.822
Q561.212
Q683.072
PQREnb 60.891
PE & SE 60.892
PE 53.452
Q134.622
Q235.742
Q390.001
EG 68.331
Q460.001
Q580.001
Q665.001

Final pivot table would show only 1 city at a time i.e based upon the user selection in the map Eg.if City 'ABC' selected follwoing would be the output:

   

CityLevel1Level2Level3Level4ScoreRank
ABCEnb 76.341
PE & SE 76.341
PE 84.991
Q184.621
Q290.351
Q380.002
EG 67.702
Q458.822
Q561.212
Q683.072

I hope I was clear in explaining my query.

Thanks in advance

26 Replies
Not applicable
Author

Hi Sunny,

Level 3 EG for City ABC is Rank 2 as the Avg Score is 67.70

Level 3 EG for City PQR is Rank 1 as the Avg Score is 68.33

I hope that clarifies.

sunny_talwar

Try this:

Pick(Dimensionality()-1,

Aggr(NODISTINCT Rank(Sum(Score)), Level1, City),

Aggr(NODISTINCT Rank(Sum(Score)), Level2, City),

Aggr(NODISTINCT Rank(Sum(Score)), Level3, City),

Aggr(NODISTINCT Rank(Sum(Score)), Level4, City))

Not applicable
Author

Hi Abhishek,

That doesnot give the expected result

Not applicable
Author

Dude that worked.

Thanks a ton.

My last question, as this pivot would be filter base upon the city in the map, can i use set analysis within thi cod

sunny_talwar

You can use set analysis, but what exactly are you hoping to do?

Not applicable
Author

Hi Sunny,

The requirment is like this:

On the map we are showing Rank of each city.

When we click on the a specific city in the map, we are suppose to get the detailed information about the selected city(which we just created with your help).

Now as per the default functionality of qlikview, if we select any click, the data gets filtered and we would see rank as  1 every where, which does not give any insight.

Instead, the requirement is to show the rank if the selected city from the map in comparison to the other cities.

For example: if the user has selected city "ABC" from the map following is the output desired in the final pivot:

   

CityLevel1Level2Level3Level4ScoreRank
ABCEnb 76.341
PE & SE 76.341
PE 84.991
Q184.621
Q290.351
Q380.002
EG 67.702
Q458.822
Q561.212
Q683.071
sunny_talwar

Try this:

Pick(Dimensionality()-1,

Aggr(NODISTINCT Rank(Sum({<City>}Score)), Level1, City),

Aggr(NODISTINCT Rank(Sum({<City>}Score)), Level2, City),

Aggr(NODISTINCT Rank(Sum({<City>}Score)), Level3, City),

Aggr(NODISTINCT Rank(Sum({<City>}Score)), Level4, City))

Not applicable
Author

Nailed it men !!!

All queries solved.

Really appreciate, your promptness and willingness to assist me on the query.

Regards,

Hitesh

sunny_talwar

I am glad I was able to help

Not applicable
Author

Hi Sunny,

I rank your code on my production data and facing a challenge with Dense Rank as below:

Tried to find if we have any dense rank fucntion in qlik, but no luck.

Thanks in advance.