topic Re: Grouping Dimension & Expression in pivot in QlikView

Grouping Dimension & Expression in pivot

userid128223 — Wed, 08 Mar 2017 17:11:07 GMT

I have following pivot table: (just for example)

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:

Anonymous — Wed, 08 Mar 2017 17:39:30 GMT

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 — Wed, 08 Mar 2017 20:21:05 GMT

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 — Thu, 09 Mar 2017 01:53:08 GMT

What will be the expression to match the dimension.

Anonymous — Thu, 09 Mar 2017 04:49:36 GMT

???

userid128223 — Thu, 09 Mar 2017 21:28:17 GMT

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

vishsaggi — Thu, 09 Mar 2017 21:32:37 GMT

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

rwunderlich — Thu, 09 Mar 2017 22:28:57 GMT

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

sum(Population)

-Rob