--- schema_version: "1" id: 6ec1d9042b191f435505e99922541f9c30558070aa409567fac06ab529df089b title: computeExpression Transformation collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_saql_transformation.md canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_saql_transformation.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: 7b1a6b1e52efbf6ddc82c2f501e81129d2ab976d60b6184c4f48ca59d6fcb5e1 description: The computeExpression transformation enables you to add derived fields to a dataset. The values for derived fields aren’t extracted from the input data source. Instead, CRM Analytics generates the values using a SAQL expression, which can be based on one or more fields from the input data or other derived fields. For example, you can use an expression to assign a value to a field, concatenate text fields, or perform mathematical calculations on numeric fields. products: - CRM Analytics - Cross Cloud release: "264" --- # computeExpression Transformation The computeExpression transformation enables you to add derived fields to a dataset. The values for derived fields aren’t extracted from the input data source. Instead, CRM Analytics generates the values using a SAQL expression, which can be based on one or more fields from the input data or other derived fields. For example, you can use an expression to assign a value to a field, concatenate text fields, or perform mathematical calculations on numeric fields. ![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_note.png) Note To create a calculated column based on a formula in a Data Prep recipe, see [Formula Transformation: Create a Calculated Column Based on an Expression](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_recipe_transformation_formula.htm&release=264.0.0&type=5 "In CRM Analytics, create a column in a Data Prep recipe that displays values based on a formula calculation. The calculation can include input from other fields in the same row or across rows. For example, you can create a Profit column based on input from Revenue and Cost columns. Enter formulas in EA-SQL format. EA-SQL is a collection of standard and custom functions for numeric, string, and date data."). To do it in a Data Prep Classic recipe, see [Add a Formula Field in a Recipe](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_data_prep_recipe_formula_field.htm&release=264.0.0&type=5 "Add a formula field to calculate new values from measures and dimensions in your recipe."). The computeExpression transformation and computeRelative transformation are similar, but they have a key difference. The computeExpression transformation performs calculations based on other fields within the same row. The computeRelative transformation performs calculations based on the previous and next values of the same field in other rows. Consider these guidelines when creating a computeExpression transformation. - You can include only the following SAQL operators and functions in the expression: - [Arithmetic operators](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-operators-arithmetic.html) - [Case operator](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-operators-case.html) - [String operator](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-operators-string.html) - [Date functions](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-functions-date.html) - Multivalue fields aren’t supported. The dataflow fails if you include a multivalue field in the SAQL Expression parameter. - The values of the derived field must match its specified type. For example, set the type of the derived field to Text if the values are strings. - CRM Analytics calculates the values of derived fields in the order in which they are listed in the JSON. Thus, if you create a derived field based on other derived fields in the same computeExpression transformation, the input-derived fields must be listed first. For example, Derived\_A must be listed before Derived\_B in the following computeExpression transformation JSON snippet: ``` "CreateDerivedFields": { "action": "computeExpression", "parameters": { "source": "sourceNode", "mergeWithSource": false, "computedFields": [ { "name": "Derived_A", "type": "Text", "label": "Derived Field A", "saqlExpression": ""hello ""}, { "name": "Derived_B", "type": "Text", "label": "Derived Field B Dependent on Field A", "saqlExpression": "Derived_A + "world""} ] } } ``` - You can choose whether the resulting dataset includes only the derived fields, or includes the input and derived fields. - If the expression produces an overflow value for a numeric field, the transformation sets the field value to the default value specified for the column. If no default value is set and null handling is disabled, the field value is set to 0. If no default value is set and null handling is enabled, it’s set to null. (See the documentation about [null measure handling](https://help.salesforce.com/articleView?id=bi_integrate_null_measures.htm&language=en_US).) Also, if the expression defines multiple columns and the expression produces an overflow value, all generated fields are set to the default value. To isolate the issue to the field with the overflow value, create a separate computeExpression transformation for each defined column. ![Example](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_example.png) Example Let’s look at an example. You want to create a dataset based on Salesforce opportunity data. You create a dataflow that extracts the Id and Amount fields from the Opportunity object. In addition, you also want to add the following derived fields to the dataset: ModifiedId, SalesTax, FinalPrice, and ValueCategory. For the derived fields, you: - Append “SFDC” to each opportunity Id to get a new modified Id. - Calculate the sales tax based on an 8% tax rate. - Calculate the final price by adding the amount and sales tax. - Categorize opportunities into low-, medium-, and high-value buckets based on the calculated final price. You create the following dataflow definition. ``` { "salesData": { "action": "sfdcDigest", "parameters": { "object": "Opportunity", "fields": [ {"name": "Amount"}, {"name": "Id"}]}}, "Derived_Fields": { "action": "computeExpression", "parameters": { "source": "salesData", "mergeWithSource": true, "computedFields": [ { "name": "ModifiedId", "type": "Text", "saqlExpression": ""SFDC" + Id"}, { "name": "SalesTax", "type": "Numeric", "precision": 18, "defaultValue": "0", "scale": 5, "saqlExpression": "Amount * 0.08"}, { "name": "FinalPrice", "type": "Numeric", "precision": 18, "defaultValue": "0", "scale": 5, "saqlExpression": "Amount + SalesTax"}, { "name": "ValueCategory", "type": "Text", "saqlExpression": "case when FinalPrice < 1000 then "Low" when FinalPrice >= 1000 and FinalPrice < 2000 then "Medium" else "High" end"} ] } }, "Register_CategorizedSales": { "action": "sfdcRegister", "parameters": { "alias": "Categorized_Sales", "name": "Categorized_Sales", "source": "Derived_Fields" } } } ``` - **[computeExpression Parameters](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_saql_transformation_operation.htm&release=264.0.0&type=5)** When you define a computeExpression transformation, you set the action attribute to `computeExpression`. You also specify the parameters for the input source and the expression used to generate the values.