Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have following pivot table: (just for example)
County | City | Population (expression) |
---|---|---|
US | NY | 5 |
Florida | 40 | |
Boston | 2 | |
LA | 1 | |
Phoenix | 2 | |
Canada | Toronto | 35 |
Kingston | 2 | |
Calgary | 1 | |
Vancouver | 2 |
Dimensions = Country & City
Expression = population.
Now i want to group some City dimension together and similarly group their expression as well how do i accomplish that.
Results:
County | City | Population (expression) | |
---|---|---|---|
US | NY, Boston | 7 | |
LA, Phoenix | 3 | ||
Florida | 50 | ||
Canada | Toronto, Kingston | 37 | |
Calgary | 1 | ||
Vancouver | 2 |
Extending Robin's expression: add City in your aggregation.
BTW how you got 50 for City Florida, was it a typo or were you adding total to your city Florida?
Like below.
Dim:
= Country
= aggr(if(match(City,'NY','Boston'),'NY, Boston',
if(match(City,'LA','Phoenix'),'LA, Phoenix',
if(match(City,'Toronto','Kingston'), 'Toronto, Kingston',
City))), Country, City )
calculated dimension:
aggr(if(match(City,'NY','Bostom),'NY, Boston',
if(match(City,'LA','Phoenix'),'LA, Phoenix',
if(match(City,'Toronto','Kingston'),'Toronto, Kingston',
City))),County)
Extending Robin's expression: add City in your aggregation.
BTW how you got 50 for City Florida, was it a typo or were you adding total to your city Florida?
Like below.
Dim:
= Country
= aggr(if(match(City,'NY','Boston'),'NY, Boston',
if(match(City,'LA','Phoenix'),'LA, Phoenix',
if(match(City,'Toronto','Kingston'), 'Toronto, Kingston',
City))), Country, City )
What will be the expression to match the dimension.
???
so how would you group the population based on grouping criteria.
The aggr() function used above is grouped by Country. So it would automatically look for matching based on Country grouping.
The Dimension takes care of the grouping. The expression remains the same:
sum(Population)
-Rob