Derived fields for Data Processing (Overview / Create / Aggregate)
The general purpose of Datasets functionality, as well as newly introduced Derived fields, is to allow users manage data from any Data Source in the simple intuitive manner, with no need to master the specifics of syntax which are commonly present 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:
EXAMPLE 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: You can 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 this 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 offers an ability to include current and prior variables to the mathematical formula (as shown in the Example 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 your column.
- Precision: you can define the number of decimals to be displayed in the Results set.
- Expression: define the formula for calculating new required values. For more details refer to: Expression Syntax for Derived Fields Formulas. Only NON-Derived fields are available for constructing formulas for derived fields.
- Click Add Field. The pop-up closes, then make sure to Apply Changes, otherwise the Results set is not going to be updated with a new column.
- A new column with calculated values is added to the Results set.
Rules of aggregation of Derived fields
Aggregation can be applied to Derived fields (Sum, Avg, Min, Max, Count). Things to consider:
- Aggregation cannot be applied to a Derived field if it is already applied to one of the columns that are 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 detauiled instructions, refer to: Create a Metric from a Dataset View
- Value Column: Select a Derived field from the drop-down list.
- Aggregate using: Refer to rules of Aggregation described above.