Salesforce Docs Portal

computeRelative Parameters

When you define a computeRelative transformation, you set the action attribute to computeRelative. You also specify the parameters for the input source, partition-by field, sort field, and derived field definitions.

Parameters

You can specify parameters in the following sections of the computeRelative node.

The following table describes the parameters in the parameters section.

Parameter Required? Value
source Yes Node in the dataflow definition file that identifies the input source for this transformation.
partitionBy Yes API name of the field used to partition the records in the dataset. Specify one partition-by field only.
orderBy Yes

Field used to sort the records within each partition and the sort order: ascending (asc) or descending (desc). Specify one sort field only.

Example: 

"orderBy": [
    {                
        "name":"CreatedDate", 
        "direction":"asc"
     }
]
computedFields Yes

A list of definitions for derived fields. Derived fields can be based on a source field or on a SAQL expression.

Example showing derived fields based on a source field and on a SAQL expression: 

"computedFields": [
                {
          "name": "PreviousAmount",
          "label": "Previous Amount",
          "description": "Previous amount of opportunity",
          "expression": {
            "sourceField": "Amount",
            "offset": "previous()",
            "default": "null"
          }
        },
        {
          "name": "AmountChange",
          "label": "Amount % Change",
          "description": "Percentage change from previous amount",
          "expression": {
            "saqlExpression": "(current(Amount)-previous(Amount))/previous(Amount)*100",
            "type": "Numeric",
            "scale": 2,
            "default": "null"
          }
        }
      ]

computedFields

The following table describes the attributes in the computedFields section.

Parameter Required? Value
name Yes API name of the derived field to add to the dataset. The name must be unique in the dataset. If the name is not unique, the dataflow fails to run.
label No The display name of the derived field that appears in the CRM Analytics user interface. Can be up to 255 characters. Defaults to the API name if not specified.
description No Description of the derived field for information only.
expression Yes

Expression attributes used to calculate the value for the derived field. The expression can be based on input fields or other derived fields in the transformation.

Example: 

"expression": {
    "sourceField": "CloseDate",
    "offset": "previous()",
    "default": "01-01-1970"

expression

The following table describes the attributes in the expression section when creating a derived field based on a source field.

Parameter Required? Value
sourceField Yes API name of the input field from the source node that’s used in the expression.
offset Yes

The function used in the expression. You can use the following functions:

current()
Gets the value from the current record.
Example: 
"offset": "current()"
first()
Gets the value from the first record in the partition, like the first CreateDate for an opportunity.
Example: 
"offset": "first()"
next()

Gets the value from the next record.

Example: 
"offset": "next()"
previous()
Gets the value from the previous record.
Example: 
"offset": "previous()"

Derived fields are computed in the order that they’re defined. The calculation of a derived field can be based on the value from another derived field as long as it has already been defined. For example, next() can’t access the value of a derived field in the next row.

To get the correct results when using the previous() and next() functions, the computeRelative transformation requires you to sort the records.
default Yes (for numeric fields only)

The default value if one can’t be calculated. For example, you can specify a default value when no previous or next value exists. You can insert a constant value or current() as a default value.

Examples:

"default": "3000-01-01T00:00:00.000Z" 
"default": "current()"

The following table describes the attributes in the expression section when creating a derived field based on a SAQL expression.

Parameter Required? Value
saqlExpression Yes

SAQL expression used to calculate the value for the derived field. The expression can be based on input fields or other derived fields in the transformation. You can use the offset functions current(),first(),next(), and previous() in the expression.

Example to calculate the percentage change from the previous amount to the current amount:

"saqlExpression": "(current(Amount)-previous(Amount))/previous(Amount)*100"

You can also use the SAQL case operator in the expression.

Example to output Up, Down, or No Change values based on the change from the previous amount to the current amount: 

 "saqlExpression": "case when current(Amount)>previous(Amount) then "Up" when
      current(Amount)<previous(Amount) then "Down" else "No Change" end"
type Yes

CRM Analytics field type associated with the field. Valid types are Date, Numeric, and Text.

Example:

"type": "Text"
scale No

The number of digits to the right of the decimal point in a numeric value.

Example:

"scale": 2
default Yes (for numeric fields only)

The default value if one can’t be calculated.

Example:

"default": "null"