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

Get rank from table

Dear all,

I have loaded from excel the following file:

CountryCategoryCodeAmount
GR11.15
GR11.210

GR

22.115
GR22.220
US11.125
US11.230
US11.335
US22.140
US33.145
US33.250
US33.3100
US44.160

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 :

CategoryAverage
17,5
217,5
30
40

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

CategoryAverage
130
240
365
460

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 :

CategoryAverageRank
17,52
217,52
302
402

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

CategoryAverageRank
1301
2401
3651
4601

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

Thank you in advance

12 Replies
Not applicable
Author

Hey,

Qlikview is case sensitive so vRank is not the same as vrank

try this expression in your file and you should be fine:

If(isnull(GetFieldSelections(Country)),'Select Country', (($(vrank))))

R

Not applicable
Author

Mr Rahul it just worked for this example but when i tried with the data in the excel i have attached, the results of the third column are not correct.

In the excel you will see the following data:

CountryCategoryCodeAmount
GR11.125
GR11.230
GR22.115
GR22.220
US11.11
US11.22
US11.33
US22.140
US33.145
US33.250
US33.3100
US44.160

With the above data the correct values on the table should not be the following:

(For category 1 when US is selected it should rank in 2 and not 1 because GR country's Avg(amount) for category 1 is 27,5>2)

Screenshot_1.jpg

Screenshot_2.jpg

Not applicable
Author

I think that the change should be on the definition of vrank variable. What is your opinion?