Grouping Dimension & Expression in pivot

[userid128223]

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

[Anonymous]

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)

[vishsaggi · Accepted solution]

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 )

[userid128223]

What will be the expression to match the dimension.

[Anonymous]

???

[userid128223]

so how would you group the population based on grouping criteria.

[vishsaggi]

The aggr() function used above is grouped by Country. So it would automatically look for matching based on Country grouping.

[rwunderlich]

The Dimension takes care of the grouping. The expression remains the same:

  sum(Population)

-Rob