Data Discovery: Calculated Fields

👤 This documentation is intended for SQL Users. 

Note: Calculated Fields are currently supported with Data Discovery. Data Discovery is available on select plans. Site administrators can contact their Customer Success Manager for additional information.

For information on Data Discovery, see our documentation here

For how to create a dataset for use in discover mode, see our documentation here

For Data Discovery FAQs, see our documentation here

Calculated Fields

Data Discovery users can perform basic calculations and aggregations against their datasets using Calculated Fields. This allows for the creation of dynamic ratios, custom categories and groupings, basic arithmetic calculations, and more.

To create a calculated field, choose a published dataset by clicking the green + sign:

Add the Field Name, Formula and click Validate:

Once the formula Validates successfully, click Save Field. For example, to add a calculated field of 'Profit':

The calculated field 'Profit' will show up in the data fields on the left-hand side of the editor.

This calculated field can now be used as a field from your dataset!

Calculated Fields Examples

Dynamic Ratios

• A ratio that dynamically calculates as the underlying data changes.
• If dividing 2 integers, multiply the numerator by 1.0 to return a decimal output
• Ex: Clickthrough Rate = SUM(clicks) * 1.0 /SUM(impressions)

Basic Math

• Simple arithmetic between columns in your dataset
• Ex: Profit = Revenue - Cost

Date Functions

• Easily calculate the difference between two dates, or between a date and the current date.
• Ex: DATEDIFF(day, project_start, project_end)

Create Categories and Groupings

• Groupings and categories can be created if they do not already exist in the dataset using Case When statements.
• Ex: CASE WHEN customers < 5 , THEN 'SMB', ELSE 'Majors'

Combining Aggregations

• Get a result across data aggregations
• Ex: Revenue = SUM(profit) - SUM(cost)

Percent of a Whole

• Get a percentage of a segment over a whole
• Ex (percent of revenue that comes from the iOS platform): SUM(CASE WHEN [Platform]='iOS' THEN [Price] else 0 end) / SUM([Price])