7 Replies Latest reply: Mar 9, 2017 5:28 PM by Rob Wunderlich

# Grouping Dimension & Expression in pivot

I have following pivot table: (just for example)

CountyCityPopulation (expression)
USNY5
Florida40
Boston2
LA1
Phoenix2
Kingston2
Calgary1
Vancouver2

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:

CountyCityPopulation (expression)
USNY, Boston7
LA, Phoenix3
Florida50
Calgary1
Vancouver2
• ###### Re: Grouping Dimension & Expression in pivot

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)

• ###### Re: Grouping Dimension & Expression in pivot

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 )

• ###### Re: Grouping Dimension & Expression in pivot

What will be the expression to match the dimension.

• ###### Re: Grouping Dimension & Expression in pivot

???

• ###### Re: Grouping Dimension & Expression in pivot

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

• ###### Re: Grouping Dimension & Expression in pivot

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

• ###### Re: Grouping Dimension & Expression in pivot

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

sum(Population)

-Rob