Cloudera Docs

Cross tabulation

Cross Tabulation visual is a tool that allows you to compare the relationship between two variables in table format. CDP Data Visualization enhances the understanding of relative value size by assigning more saturated colors to the table fields with larger values.

The following steps demonstrate how to create a new Cross Tabulation visual on the World Life Expectancy dataset. For an overview of shelves that specify this visual, see Shelves for cross tabulation.

  1. Start a new visual based on the World Life Expectancy dataset.
    For instructions, see Creating a visual.
  2. In the VISUALS menu, find and click Cross Tabulation.

    The shelves of the visual changed. They are now Column, Row, Measures, Tooltips, and Filters. The Measures shelf is mandatory.

  3. To show specific items, populate the shelves from the available fields in the DATA menu.
    • Under Dimensions, select 'country' and drag it to the Column shelf.
    • Under Dimensions, select 'year' and drag it to the Row shelf.
    • Under Measures, select 'life_expectancy' and drag it to the Measures shelf.

      By default, the sum aggregate of the field appears on the shelf, as 'sum(life_expectancy)'.

  4. Change the aggregate of the 'life_expectancy' field from 'sum(life_expectancy)' to 'avg(life_expectancy)'.
    1. On the Measures shelf of the visual, click the caret-right icon to the right of the field.
    2. In the FIELD PROPERTIES menu, click the caret-right icon next to Aggregates to open a drop-down menu..
    3. From the list of aggregate functions, select Average.
    4. Click the x icon at the top of the FIELD PROPERTIES menu to close it.

    The shelf now contains the modified field with the 'avg()' aggregation function.

    Alternatively, you can change the aggregation at the level of the dataset, as described in Changing field aggregation.

  5. Click REFRESH VISUAL.

    The updated Cross Tabulation visual appears. It shows life expectancy information for each row of the dataset. By default, Cross Tabulation visuals show smaller values in light colored cells, and larger values in darker colored cells.

  6. Optional: On the Filters shelf, you can add several Dimensions and Measures from the DATA menu.

    This enables you to dynamically control the data input, and discover the data at a more granular level.

    • For example, from Dimensions, drag 'un_subregion' to the Filters shelf. In this example, the filter value is set to 'Middle Africa'.

    • To limit the time interval, add the 'year' field to the Filters shelf.

      In the Filter for year modal window, click the Range tab, select the last 30 years of the dataset, 1981 through 2010, and lick APPLY.

  7. Optional: To improve the appearance of the visual, alias the 'country' field as 'Country' and the 'life_expectancy' field as 'Life Expectancy'.
    1. On the shelf of the visual, click the caret-right icon to the right of the field.
    2. In the FIELD PROPERTIES menu, click the caret-right icon next to Alias.
    3. In the text box below Alias, enter the alias name of the column, as it should appear in the visual.
    4. Click the x icon at the top of the FIELD PROPERTIES menu to close it.

    The shelves now show the columns with their alias names.

  8. Adjust the display format for the 'Life Expectancy' field to use a comma thousands separator, and to carry zero decimal places.
    1. On the Measures shelf of the visual, click the caret-right icon to the right of the field.
    2. In the FIELD PROPERTIES menu, click Display Format.
    3. In the Display Format modal window, select the Category 'Real Number'.
    4. In the Decimal Places text box, set '0'.
    5. Select the Use 1000 separator option, and click SAVE.
    6. Click x icon at the top of the FIELD PROPERTIES menu to close it.
  9. Optional: To provide more detailed information, add some fields to the Tooltips shelf.

    For example, drag the Dimensions 'un_region' and 'un_subregion', and the Measures 'population' and 'gdp_per_capita' to the Tooltips shelf.

  10. Click REFRESH VISUAL.

    The updated Cross Tabulation visual appears. It demonstrates the relative size of populations by assigning a progressively more saturated color to represent increasingly larger values.

  11. Click the pencil/edit icon next to the title of the visualization to enter a name for the visual.

    In this example, the title is changed to 'World Life Expectancy - Cross Tabulation'. You can also add a brief description of the visual as a subtitle below the title of the visualization.

  12. At the top left corner of the Visual Builder, click SAVE.