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, Arcadia Enterprise enables you to create new fields at the level of the dataset; they can then be used in visuals and dashboards, just the same as native data fields.

The following steps demonstrate how to create a new field gdp (gross domenstic product) in the dataset World Life Expectancy [data source samples.world_life_expectancy]. We define it by the following equation:
gdp = gdp_per_capita x population
Note that Arcadia Enterprise supports three primary methods of editing fields at the dataset level: Basic, Expression, and Display Format. Here, we use make changes both on the Basic and Expression tabs. We show how to use Display Format options in Changing the Field Display Format.
  1. On the main navigation bar, click Data.

    Click DATA on main nav

    The Data view appears, open on the Datasets tab.

    Main landing page of DATA
  2. In the left navigation menu, click samples.
  3. In the Datasets area, select World Life Expectancy (samples.world_life_expectancy).

    selecting a dataset
    Selecting Dataset 'World Life Expectancy'
  4. In the Dataset Detail menu, select Fields.

    detail of 'World Life Expectancy'
    Dataset Detail for 'World Life Expectancy'
  5. In the Fields interface, select Edit Field.

    Editing Fields of Dataset 'World Life Expectancy'
    Editing the Fields of Dataset 'World Life Expectancy'
  6. Under Measures, find the field gdp_per_capita, and click the (down arrow) icon on its right side.

    From the menu, select Clone.

    Cloning a field of a dataset
    Cloning a Field of a Dataset
  7. 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.

    Editing a cloned field of a dataset
    Editing a Cloned Field of a Dataset

    The Edit Field Parameters window modal appears.

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

    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.
    4. Click Edit Expression tab.
    5. Change Expression to [gdp_per-capita] * [population].
    6. Click Validate Expression to ensure that the calculation works.
    7. When the Validation Successful message appears on the modal, click Apply.
    Saving changes to field definition
    Saving Changed Field Definition
  9. Notice that the new calculated field has an equal sign notation, =.

    Under Dataset: World Life Expectancy, click Save.

    Saving changes to field definition
    Saving Changed Field Definition

Testing the Calculated Field

To test that the new calculated field works correctly, follow these steps:

  1. Click New Dashboard in the top left corner of this interface.

  2. In the new dashboard, click New Visual.

  3. Hover over the top right corner of the visual, and click the (pencil/edit) icon.

    Opening a visual in edit mode
    Saving Changed Field Definition
  4. In the Select the Combo visual type, and populate the shelves from the available fields (Dimensions, Measures, and so on) in the Data menu.

    • Under Dimensions, select country, and place it on the Dimensions shelf.
    • Under Dimensions, select year, and place it on the Filters shelf. In the Filter for year modal window, under the Value tab, select the year 2010.
    • Under Dimensions, select un_region, and place it on the Filters shelf. In the Filter for un_region modal window, under the Value tab, select Europe.
    • Under Measures, select gdp_per_capita, and place it on the Bar Measure shelf.
    • Under Measures, select gdp, and place it on the Line Measure shelf.

      Click the (down arrow) on GDP field, and in the Field Properties, under Axis, select Secondary Axis.

      Applying Secondary Axis
      Applying Secondary Axis

    Click Refresh Visual.

  5. As you can see, 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.

    Visual with original data fields and a calculated field
    Visual Uses a Calculated Field