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
- Video Tutorial
- Derived Fields Overview
- How to add Derived Fields to a Dataset
- Rules of Aggregation for Derived Fields
- How to use Derived Fields for creating Elements and Metrics
SAMPLE USE CASE
- Challenge: Calculate % of decrease of Units Sold and show it in Results
- Solution: Add a derived field with arithmetic formula
- Result: % Decrease of units sold is shown as an additional field in the Dataset Results
Challenge: Calculate % of decrease of Units Sold and show it in Results
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
Add a Derived Field with a formula that calculates the percentage of decrease.
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
The percentage per each row is included as a new field in a Results set.
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
How to add Derived Fields to a Dataset
To add a Derived Field to a Dataset:
- Access Dataset Viewer > find the Select Fields check list
- Below the existing fields click [+ Derived Field]
- 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.
- Click Add Field. The pop-up closes, click Apply Changes
- If not applied, the Results set is not updated with the new column
- 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
Derived Fields for creating 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:
- Access Actions > Build Metric. For detailed instructions, refer to: Create one or multiple Metrics from a Dataset View (Version 5.1 and beyond)
- Value Column: Select a Derived field from the drop-down list
- Aggregate using: Refer to rules of Aggregation described above