topic Re: Rank in QlikView

Rank

Thu, 22 Dec 2016 09:53:02 GMT

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:

Year	City	Level1	Level2	Level3	Level4	Score
2015	ABC	Enb	PE & SE	PE	Q1	84.62
2015	ABC	Enb	PE & SE	PE	Q2	90.35
2015	ABC	Enb	PE & SE	PE	Q3	80.00
2015	ABC	Enb	PE & SE	EG	Q4	58.82
2015	ABC	Enb	PE & SE	EG	Q5	61.21
2015	ABC	Enb	PE & SE	EG	Q6	83.07
2015	PQR	Enb	PE & SE	PE	Q1	34.62
2015	PQR	Enb	PE & SE	PE	Q2	35.74
2015	PQR	Enb	PE & SE	PE	Q3	90.00
2015	PQR	Enb	PE & SE	EG	Q4	60.00
2015	PQR	Enb	PE & SE	EG	Q5	80.00
2015	PQR	Enb	PE & SE	EG	Q6	65.00

Complete Pivot table output:

City	Level1	Level2	Level3	Level4	Score	Rank
ABC	Enb				76.34	1
		PE & SE			76.34	1
			PE		84.99	1
				Q1	84.62	1
				Q2	90.35	1
				Q3	80.00	2
			EG		67.70	2
				Q4	58.82	2
				Q5	61.21	2
				Q6	83.07	2

PQR	Enb				60.89	1
		PE & SE			60.89	2
			PE		53.45	2
				Q1	34.62	2
				Q2	35.74	2
				Q3	90.00	1
			EG		68.33	1
				Q4	60.00	1
				Q5	80.00	1
				Q6	65.00	1

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:

City	Level1	Level2	Level3	Level4	Score	Rank
ABC	Enb				76.34	1
		PE & SE			76.34	1
			PE		84.99	1
				Q1	84.62	1
				Q2	90.35	1
				Q3	80.00	2
			EG		67.70	2
				Q4	58.82	2
				Q5	61.21	2
				Q6	83.07	2

I hope I was clear in explaining my query.

Thanks in advance

Re: Rank

Thu, 22 Dec 2016 10:21:43 GMT

Use the below expression

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

Re: Rank

Thu, 22 Dec 2016 11:03:05 GMT

Dear Umamaheswara,

When i try Aggr(Rank(Avg(Scores)),City).It doesnot yield the desired answer.

I even tired Aggr(Rank(Avg(Scores)),City,Level4). It also doesnot yeild the desired answer

Re: Rank

sunny_talwar — Thu, 22 Dec 2016 11:07:15 GMT

How about this?

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

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

Re: Rank

sunny_talwar — Thu, 22 Dec 2016 11:10:29 GMT

Can't really focus, but may be City might come before Level3

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

Re: Rank

Thu, 22 Dec 2016 11:32:46 GMT

Dear Sunny,

=Aggr(NODISTINCT Rank(Avg(Score2)),Level4,City2) worked for me as below

However there is one issue when we collapse the pivot, the ranks become null

Re: Rank

sunny_talwar — Thu, 22 Dec 2016 11:47:12 GMT

Would you be able to share your qvw for us to play around with?

Re: Rank

Thu, 22 Dec 2016 11:47:41 GMT

Hi Hitesh - If you are using HRank(), drag the last dimension to make it horizontal. For an instance, once you create the pivot table, drag level 4 as horizontal dimension.

Re: Rank

Thu, 22 Dec 2016 12:00:33 GMT

PFA

Re: Rank

sunny_talwar — Thu, 22 Dec 2016 12:04:03 GMT

See how Level3 = EG is made up of both 2 and 1, what would you want to see when collapsed? 2 or 1?

Re: Rank

Thu, 22 Dec 2016 12:08:53 GMT

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.

Re: Rank

sunny_talwar — Thu, 22 Dec 2016 12:12:39 GMT

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

Re: Rank

Thu, 22 Dec 2016 12:12:52 GMT

Hi Abhishek,

That doesnot give the expected result

Re: Rank

Thu, 22 Dec 2016 12:19:14 GMT

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

Re: Rank

sunny_talwar — Thu, 22 Dec 2016 12:24:34 GMT

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

Re: Rank

Thu, 22 Dec 2016 12:31:01 GMT

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:

City	Level1	Level2	Level3	Level4	Score	Rank
ABC	Enb				76.34	1
		PE & SE			76.34	1
			PE		84.99	1
				Q1	84.62	1
				Q2	90.35	1
				Q3	80.00	2
			EG		67.70	2
				Q4	58.82	2
				Q5	61.21	2
				Q6	83.07	1

Re: Rank

sunny_talwar — Thu, 22 Dec 2016 12:43:26 GMT

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

Re: Rank

Thu, 22 Dec 2016 12:48:17 GMT

Nailed it men !!!

All queries solved.

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

Regards,

Hitesh

Re: Rank

sunny_talwar — Thu, 22 Dec 2016 12:49:28 GMT

I am glad I was able to help

Re: Rank

Fri, 23 Dec 2016 10:01:52 GMT

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.