topic Re: Get rank from table in QlikView

Get rank from table

Fri, 01 Aug 2014 08:34:53 GMT

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

Re: Get rank from table

Fri, 01 Aug 2014 08:55:39 GMT

Hi,

Please see file attached.

Regards

Re: Get rank from table

Fri, 01 Aug 2014 09:10:53 GMT

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.

Re: Get rank from table

Fri, 01 Aug 2014 09:13:47 GMT

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

for expression use:

Only(Total $(vRank))

Hope that works for you.

Regards

Re: Get rank from table

Fri, 01 Aug 2014 09:19:42 GMT

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

Re: Get rank from table

Fri, 01 Aug 2014 09:25:23 GMT

Do you have a Country selected?

Regards

Re: Get rank from table

Fri, 01 Aug 2014 09:28:51 GMT

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

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

Re: Get rank from table

Fri, 01 Aug 2014 09:33:16 GMT

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

Re: Get rank from table

Fri, 01 Aug 2014 10:22:24 GMT

Hey,

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

Regards

Re: Re: Get rank from table

Fri, 01 Aug 2014 13:23:10 GMT

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

Regards

Re: Re: Get rank from table

Fri, 01 Aug 2014 13:41:45 GMT

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

Re: Re: Get rank from table

Fri, 01 Aug 2014 13:46:11 GMT

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:

Country	Category	Code	Amount
GR	1	1.1	25
GR	1	1.2	30
GR	2	2.1	15
GR	2	2.2	20
US	1	1.1	1
US	1	1.2	2
US	1	1.3	3
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

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)

Re: Get rank from table

Mon, 04 Aug 2014 09:02:40 GMT

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

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

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

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