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

[sunny_talwar]

I have not used Dense Rank before, but I read about it a little bit where you would want to see consecutive ranking. From the example I saw here (https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions) this might be a little tricky in QlikView's front end. But Rank function allows you to view the rank in few difference ways by using its 2nd and 3rd paramater. Check out the Rank function here: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/RankingFunctions/...

[#3]

Hi Sunny,

I have tried few functions like RangeAvg to get the issue resolved for Competition rank, However not able to embedd our code into it.

Can you please haev a look into it. Attaching the workbook for reference.

Thanks in Advance

[sunny_talwar]

What is the output you expect now? Can you share the numbers you are looking to get?

[#5]

Dear Sunny,

Following is the expected O/P.

[sunny_talwar]

Try this:

Pick(Dimensionality()-1,

Aggr(NODISTINCT Num(Rank(Sum({<City=>}Score), 4)), [Level1], City),

Aggr(NODISTINCT Num(Rank(Sum({<City=>}Score), 4)), [Level2], City),

Aggr(NODISTINCT Num(Rank(Sum({<City=>}Score), 4)), [Level3], City),

Aggr(NODISTINCT Num(Rank(Sum({<City=>}Score), 4)), [Level4], City))

[#7]

It worked, can you please let me know, how did NUM helped here to achieve our result.

[sunny_talwar]

Rank gives out a dual output, I just converted the text into number using Num() function. + I used Rank(Expression, 4) to get the consecutive ranks Rank ‒ QlikView