Adding a Calculated Field to a Dataset

Published on October 5, 2016

Where do we need calculated fields?

Calculated fields are used when you need to aggregate or manipulate our data in some way. For example, let’s look at the dataset Employee_Salary_2014 [Data source]. This dataset shows employee salaries in the Montgomery County, Maryland in the year 2014. It has fields showing the Current Annual Salary and the Overtime Pay received by each employee. To create a visualization Net Salary received by each employee, I’ll show you how to add a calculated field that calculates the net salary for each row, by adding the values in the Overtime Pay field to the values in the Current Annual Salary field.

In this exercise, I’ll show how to:

  • Manage dataset field attributes
  • Derive new columns in an existing dataset
  • Create and refine a visual that contains the new column

Prerequisites

You’ll need access to the dataset Employee_Salary_2014 [data source]. If you haven’t created this dataset yet, see these links below to:

Prepare the Dataset

  1. Find and open the specific dataset.
  2. In the left side navigation menu, click Attributes.
    Attributes view appears, and shows the Dimensions and Measures lists
    image03
  3. If the lists are collapsed, click Expand All at the top of the Measures list.
    image04
  4. Find Edit Attributes at the top of the Dimensions field.
    image00
  5. Click Edit Attributes.
    The Dimensions and Measures lists appear, with editable fields highlighted in blue.
    image08

Convert Dimensions into Measures

Sometimes imported data does not naturally divide into dimensions and measures. In such a scenario, you can declare and manipulate the dataset by converting Dimensions into Measures and vice versa (this is a toggle function).

  • Convert Dimensions into Measures by clicking the Dim in the left of the rows you want to convert to measures. Make sure you set the field type to Integer:
    pasted-image-0

Create a new calculated field in the dataset

  1. Click Edit Attributes.
    The Dimensions and Measures lists appear.
    image06
  2. In the Measures list, find the field current_annual_salary.
  3. Click Clone next to the field name.
    image01
    A new field named Copy of current_annual_salary appears below the current_annual_salary row.
    image02
  4. Click Copy of current_annual_salary.
    A pre-populated Column View window appears.
  5. Make the following changes in the Column View modal fields:
    • Change Display Name to Net Salary Received 2014.
    • In the Expression field, enter the following expression, and click Validate.
      [current_annual_salary] + [2014_overtime_pay]
      A confirmation message appears.
    • Click Applyimage09
  6. Note that the new measure Net Salary Received 2014 appears in the measures list.
    image
  7. Click Save

To use the new column in a visualization

  1. Click New Visual at the top right of the main screen.
    A new table appears, showing the calculated field Net Salary Received 2014.
    image07
    Optionally, you can adjust the visual by editing the table header row and cell format.
  2. To learn how edit the field names, or table header row, follow the instructions in Alias. Here, I made the following changes:
    • Field gender has alias Gender.
    • Field current_annual_salary has alias Annual Salary 2014.
    • Field 2014_gross_pay_received has alias Gross Pay Received 2014.
    • Field 2014_overtime_pay has alias Overtime Pay 2014.
    • Field department has alias Department.
      image10
  3. To format the cells in the data table, follow the steps mentioned here.

 

 

Related links:

  1. Creating Datasets
  2. Finding Datasets
  3. Dataset Details
  4. Related Apps
  5. Removing Datasets
  6. Working with Data Models