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 nonaggregate 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
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])*100100)
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 inline.
 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 popup 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 NONDerived fields are available for constructing formulas for derived fields.
 Click Add Field. The popup 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 dropdown list
 Aggregate using: Refer to rules of Aggregation described above
0 Comments
Add your comment