Cloudera Docs

Creating calculated fields

In Cloudera Data Visualization, you can create a calculated field in a dataset when you need to transform source data or calculate a value from one or more fields. You can then reuse the field across dashboards and visuals instead of adding the expression to each visual.

The following steps show you how to create a new field gdp (gross domenstic product) in the World Life Expectancy dataset [data source: main.world_life_expectancy], using the following equation:
gdp = gdp_per_capita x population
  1. On the left navigation panel, click Data.

    The Data view opens, displaying the Datasets tab.

  2. Select a connection from the connection list on the left side panel.
  3. In the Datasets area, click the dataset you want to modify.
  4. In the Dataset Detail menu, select Fields.
  5. Click Edit to access the editing interface.

    This action adds an edit option () to each dataset field row.

  6. Find the field you want to use for calculated fields.
  7. Click > Duplicate on the field row.
  8. Find the new, duplicated field and click on its right side.

    The Edit Field Parameters modal opens.

  9. Make the following chnages on the Basic Settings tab:
    1. Change Display Name to gdp.
    2. Add gdp_per_capita * population to Field Comment.
    3. Update the Default Aggregation to Sum.
  10. Switch to the 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. If the validation is successful, click Apply.

    The calculated field is marked with = on the field row.

  11. Click Save.

Testing calculated fields

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

The following steps show you how to test the calculated field gdp (gross domenstic product) in the World Life Expectancy dataset [data source: main.world_life_expectancy].

  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, on the Value tab, select the year 2010 and click APPLY.
    4. Under Dimensions, select un_region, and place it on the Filters shelf.
    5. In the Filter for un_region modal, on the Value tab, select Europe and click APPLY.
    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 graph overlays the two measures: bars show the original gdp_per_capita values, and a line shows the calculated gdp values.