Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Try with pick,
regards
Darek
Please let me know how do you like example, i have attached into previous
Regards
Darek
Great!!!
SecondaryDimensionality() and Dimensionality(): never tried it, but that's exactly what I was looking for.
I also found an interesting article on this:
Hi,
Could u pls explain the expression u have written so it would be easier for us to understand
?
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