Understanding Derived Fields

The general purpose of Datasets functionality, as well as the ability to included Derived fields, is to allow users to manage data from any Data Source in a simple intuitive manner, with no need to master the specifics of syntax commonly used in various databases or Business Intelligence services.

Derived fields include values that do not exist in a Data Source itself but are calculated from one or more existing numeric fields via basic arithmetic expressions and non-aggregate numeric functions.

This article covers:

Sample Use Case

  1. Challenge:  Calculate % of decrease of Units Sold and show it in Results
  2. Solution: Add a derived field with arithmetic formula
  3. Result: % Decrease of units sold is shown as an additional field in the Dataset Results
Click to see each step in details

Challenge:  Calculate % of decrease of Units Sold and show it in Results

Use Case Visual

Challenge:

The Dataset View shown in the picture above is set up in a way to compare values in Current and Prior instances in order to track potential data changes. It is called "Decreased Sales" and helps to elicit those items, where the number of units sold decreased from the Prior period.

What is the percentage of decrease of Units Sold?

Solution: Add a derived field with arithmetic formula

Solution:

Add a Derived Field with a formula that calculates the percentage of decrease.

([units]/[prior units])*100-100)

NOTE: Since this formula calculates the difference for Last Two Instances (current and prior), it is not applicable for a Single Instance.

Result: % Decrease of units sold is shown as an additional field in the Dataset Results

Result:

The percentage per each row is included as a new field in a Results set.

Video Tutorial

Derived Fields Overview

Derived Fields support basic arithmetic processing as well as standard MySQL functions that can be applied in-line.

  • Formulas: Preview the formula used for a Derived Field by hovering over the respective field in the Select Fields check list. To see the list of available mathematical expressions, see: Expression Syntax for Derived Fields Formulas
  • Dataset Views: Derived fields are created and assigned to a specific Dataset View but can be duplicated together with a View
  • Filter Criteria: Derived Fields are supported in filter criteria the same way as regular Dataset source fields
  • Comparing Instances: Derived Fields are applicable both for single instances and in case of comparing instances of snapshot Datasets: in this case the system provides the ability to include current and prior variables in a mathematical formula (as shown in the Sample Use Case above). For more details refer to: Using Derived Fields when comparing Instances

Adding Derived Fields to a Dataset

  1. Access Dataset Viewer and find the Select Fields check list
  2. Below the existing fields click [+Derived Field]
  3. The Construct Derived Field pop-up opens. Specify the following:
    • Name: Provide a unique descriptive Name for the new column
    • Precision: Specify the number of decimals to be displayed in the field in the Results set
    • Expression:  Define the formula for calculating new required values. For more details refer to: Expression Syntax for Derived Fields Formulas
      • NOTE:  Only NON-Derived fields are available for constructing formulas for derived fields.
  4. Click [Add Field] and then [Apply Changes]
    • If not applied, the Results set is not updated with the new column
  5. A new column with calculated values is added to the Results set

Rules for Aggregation of Derived Fields

Aggregation can be applied to Derived Fields (Sum, Avg, Min, Max, Count). Consider that:

  • Aggregation cannot be applied to a Derived Field if it is already applied to one of the columns used in a formula
  • Last Two Instances case. Aggregation cannot be applied to a Derived Field if Current and Prior Values are used in a formula

Using Derived Fields to Create New Elements

Derived Fields can be used as Value fields both for Metrics and Reports. To create a Metric with a Derived Field used as a value field:

  1. Access Actions > Build Metric. For detailed instructions, refer to:  Create one or multiple Metrics from a Dataset View (Version 5.1 and beyond)
  2. Value Column: Select a Derived field from the drop-down list
  3. Aggregate using: Refer to rules of Aggregation described above