Cloudera Docs

Creating calculated fields

Sometimes, the data in the base tables cannot be used directly, and must use an expression to change or "correct" it. For other use cases, you can create a calculation based on one or more fields. Instead of adding these expression for every visual, in CDP Data Visualization, you can easily create a new calculated field in the dataset, and subsequently use it in dashboards and visuals.

The following steps demonstrate how to create a new field gdp (gross domenstic product) in the dataset World Life Expectancy [data source main.world_life_expectancy]. We define it by the following equation:
gdp = gdp_per_capita x population
CDP Data Visualization supports three primary methods of editing fields at the dataset level: Basic, Expression, and Display Format. In this example, we make changes both on the Basic and Expression tabs. For innformationn on how to use Display Format options, see Changing the Field Display Format..
  1. On the main navigation bar, click Data.
  2. In the left navigation menu, click samples.
  3. In the Datasets area, select World Life Expectancy (main.world_life_expectancy).
  4. In the Dataset Detail menu, select Fields.
  5. In the Fields interface, select Edit Field.
  6. Under Measures, find the field gdp_per_capita, and click the (down arrow) icon on its right side.
  7. From the menu, select Clone.
  8. Under Measures, find the new cloned field Copy of gdp_per_capita, click the (down arrow) icon on its right side, and select Edit Fields.

    The Edit Field Parameters window modal appears, which supports three primary methods of editing fields; they match the three tabs of the modal: Basic, Expression, and Display Format.

  9. In the Edit Field Parameters modal, under the Basic tab, make the following changes:
    1. Change Display Name to gdp.
    2. Add Field Comment gdp_per_capita * population.
    3. Ensure that the Default Aggregation is Sum.
  10. Click Edit Expression tab and make the following changes.
    1. Change Expression to [gdp_per_capita] * [population].
    2. Click Validate Expression to ensure that the calculation works.
    3. When the Validation Successful message appears on the modal, click Apply.

    The new calculated field has an equal sign (=) notation.

  11. Under Dataset: World Life Expectancy, click Save.

Testing calculated fields

In CDP Data Visualization, you can easily test whether a newly calculated field works correctly or not.

  1. Click New Dashboard in the top right corner of this interface.
  2. Select the Combined Bar/Line visual type.
  3. Populate the shelves from the available fields in the following way:
    1. Under Dimensions, select country, and place it on the Dimensions shelf.
    2. Under Dimensions, select year, and place it on the Filters shelf.
    3. In the Filter for year modal window, under the Value tab, select the year 2010.
    4. Under Dimensions, select un_region, and place it on the Filters shelf.
    5. In the Filter for un_region modal window, under the Value tab, select Europe.
    6. Under Measures, select gdp_per_capita, and place it on the Bar Measure shelf.
    7. Under Measures, select gdp, and place it on the Line Measure shelf.
    8. Click the arrow on gdp field.
    9. In the Field Properties under Axis, select Secondary Axis.
  4. Click Refresh Visual.

The two measurements appear on the graph, superimposed on each other: the original gdp_per_capita represented by the bars, and the calculated gdp, represented by the line.