Cloudera Docs

Hierarchical cross tabulation visuals

has an alternate solution for hierarchical dimension representation in cross tabulation visuals.

The following steps demonstrate how to create and configure a de facto dimensional hierarchy on the shelves of a cross tabulation visual. We are using dataset World Life Expectancy [data source samples.world_life_expectancy], to build a cross tabulation table that can switch between representations at three different levels of data granularity.

  1. Start a new cross tabulation visual on the World Life Expectancy dataset.

    For more information, see Cross tabulation.

  2. Populate the shelves from the available Fields under Dimensions and Measures in the Data menu.
    • Under Dimensions, select un_region and drag it over the Dimensions shelf. Repeat with un_subregion and country, adding them under un_region. Note that your placement of these fields is from the largest to the smallest, top to bottom.
    • Under Measures, select population and drag it over Measures shelf. Ensure that the default aggregation is sum(population).
  3. On the Row shelf, select the un_region field and select Enable Expansion option in the Field Properties panel.
  4. On the Row shelf, on the un_subregion and the country field, click the Down arrow icon, and select Enable Expansion.
  5. Click Refresh Visual.

    The cross tabulation table appears with a Plus icon in the un_region, un_subregion, and country columns. This indicates that you can expand the data in these columns.

    The sum(population) column shows the combined population for all un_regions.

  6. To see more details, expand the un_region column of the visual by clicking the Plus icon under the column header.
  7. Expand the un_subregion column of the visual by clicking the Plus icon in the field next to one of the un_region. We chose to expand Africa.

    When you expand Africa, it generates a complete tabulation aggregated by un_subregion.

  8. Expand the country column of the visual by clicking the Plus icon in the field next to one of the un_subregions. We chose to expand Middle Africa.

    When you expand Middle Africa, it generates a complete tabulation aggregated by country.

  9. Change the title to Hierarchical Cross Tabulation of Population.
  10. At the top left corner of the Visual Designer, click Save.