Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
I think that the change should be on the definition of vrank variable. What is your opinion?