Rank

[#1]

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

[#2]

Use the below expression

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

[#3]

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

[sunny_talwar]

How about this?

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

or

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

[sunny_talwar]

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

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

[#6]

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

[sunny_talwar]

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

[#8]

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.

[#9]

PFA

[sunny_talwar]

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