Get rank from table

[#1]

Dear all,

I have loaded from excel the following file:

Country	Category	Code	Amount
GR	1	1.1	5
GR	1	1.2	10
GR	2	2.1	15
GR	2	2.2	20
US	1	1.1	25
US	1	1.2	30
US	1	1.3	35
US	2	2.1	40
US	3	3.1	45
US	3	3.2	50
US	3	3.3	100
US	4	4.1	60

And I have created one multi box on which there is only the dimension of Country (GR,US,ES) and one pivot table chart object which calculates the averages of codes for each category (the country is selected from the user in the multi box) and looks like the following :

For Country = GR (selected from the multibox) it returns :

Category	Average
1	7,5
2	17,5
3	0
4	0

For Country = US (selected from the multibox) it returns :

Category	Average
1	30
2	40
3	65
4	60

As you can easily understand in this pivot I have added on the dimensions tab the "Category" dimension and on the expression tab I have added one definition which calculates the average of the Codes [ Avg (Amount) ].

I now want to add one column in this pivot which will calculate the rank of the country selected for each category and average. So the result will have to be the following:

For Country = GR (selected from the multibox) it returns :

Category	Average	Rank
1	7,5	2
2	17,5	2
3	0	2
4	0	2

For Country = US (selected from the multibox) it returns :

Category	Average	Rank
1	30	1
2	40	1
3	65	1
4	60	1

Can anyone help me on the second expression of the table pivot chart object which will calculate the rank ???

Thank you in advance

[#2]

Hi,

Please see file attached.

Regards

RL

[#3]

Dear Rahul Lakhina I use Qlik View Personal Edition x64 and I cant open the .qvw because I have exceeded the maximum number of document recoveries. Please could you send me just the expression of the new column on my pivot table chart object ?? Thank you in advance mr Lakhina.

[#4]

Create a Variable vRank = Aggr( Rank( Avg({<Country=>} Amount)),Country)

for expression use:

Only(Total $(vRank))

Hope that works for you.

Regards

RL

[#5]

Unfortunately it didnt work. There is only the minus sing "-" in each row.

[#6]

Do you have a Country selected?

Regards

RL

[#7]

You could also use this as an expression to direct the users towards the country:

If(isnull(GetFieldSelections(Country)),'Select Country', (Only(Total $(vRank))))

[#8]

Of course! The only change when I change the selection of the country is the number title of the column: Only(total X).

X is changing per country on the title only. On the next expression you sent me the only added is when no country is selected it shows the "Select country" string on the rows..

[#9]

Hey,

Could you possible send me your file to see what going on? That way i can trouble shoot it better.

Regards

R

[#10]

I have attached you also the Excel file from which i loaded the data

Regards