Pivot table and Rank: show ranking of specific dimension value

[svanderwerf]

Hi,

I've been struggling with this for hours now and I can't seem to find a solution.

I want to display a table with total number of vehicles per vehicle type per country and then add a column which shows the ranking of a specific country in it (in this case 'Netherlands').

So far I've been able to make a pivot table showing the number of vehicles per type and per country and - in a seperate pivot table - the ranking of 'Netherlands' in this table:

Vehicle	Country	Belgium	France	Netherlands
Bicycles		1200	1500	3000
Cars		500	600	200
Trains		80	200	100

and

Vehicle	Dutch Ranking
Bicycles	1
Cars	3
Trains	2

I want the column 'Dutch Ranking' to be added as a column to the first table. Any thoughts?

(BTW: I attached the example QVW).

Thanks!

Stevan

[#2]

You can add the Dutch Ranking column to first table.

But it will display 2 expressions below each country like no.ofVehicles and  'Dutch Ranking'.

and in  'Dutch Ranking' it will display data only for Netherlands as u have mentioned in expression Country= 'Dutch Ranking'.

Find the attached sample for this.

[svanderwerf]

Hi Priya,

Thanks for your quick reply. I really need to display the ranking only once. Just adding it as an extra expression won't do the trick.

I also tried to add the Dutch ranking as a calculated dimension. I think that would be the way to solve this, but I can't get that to work.

[#4 · Accepted solution]

Try with pick,

regards

Darek

[#5]

Please let me know how do you like example, i have attached into previous

Regards

Darek

[svanderwerf]

Great!!!

SecondaryDimensionality() and Dimensionality(): never tried it, but that's exactly what I was looking for.

I also found an interesting article on this:

Qlikview Subtotaling Customization: Part 2 |

[#7]

Hi,

Could u pls explain the expression u have written so it would be easier for us to understand

?

[#8]

Hi,

I turned on "Show Partial Sum" for Country Dimension (Properties/Presentation Tab).

My expression is:

pick(SecondaryDimensionality()+1,only({< Country = {'Netherlands'} >} aggr( Rank( sum({< Country = >} NoOfVehicles) ), Vehicle, Country)),sum(NoOfVehicles))

Please, check what values gives in your pivot table SecondaryDimensionality().

Simple enable 2'nd expression in your pivot.

It is 1 for detailed data and 0 for our total.

pick(nbr, exp_1,expr_2, ....) lets you take expression Exp_nbr from list of expression.

So, in our case you need (as you told):

a) only({< Country = {'Netherlands'} >} aggr( Rank( sum({< Country = >} NoOfVehicles) ), Vehicle, Country)) - for totals

(SecondaryDimensionality()+1 = 1 for totals) 

b) sum(NoOfVehicles) - for detailed rows

(SecondaryDimensionality()+1 = 2 for detailed cels)

Sometimes, in more complex cases, you may need another approach, like in this thread:

Re: Num of colum with and without data

regards

Darek