Loading
CRM Analytics
Table of Contents
Filter by (0)Add
Select Filters

          No results
          No results
          Here are some search tips

          Check the spelling of your keywords.
          Use more general search terms.
          Select fewer filters to broaden your search.

            Search all of Salesforce Help
            Search all of Salesforce Help
            delta Transformation

            You are here:

            1. Salesforce Help
            2. Docs
            3. CRM Analytics

            delta Transformation

            The delta transformation calculates changes in the value of a measure (number) column in a dataset over time. The delta transformation generates an output column in the dataset to store the delta for each record. Create deltas to make it easier for business analysts to include them in queries.

            Note
            Note The delta transformation isn’t supported when null measure handling is enabled and dataflows containing delta transformations fail. Use computeRelative and computeExpression transformations instead in your dataflows, to calculate changes in measure (number) values over time. For an example, see Enable Null Measure Handling in Orgs Created Before Spring ’17.

            The delta transformation calculates each delta value by comparing the value in each record with the value in the previous record. Because records aren’t always sorted, the delta transformation orders the records before computing the delta values. To do this action, the transformation sorts the data by the specified dimension (text column), and then by the specified epoch date column.

            Note
            Note When CRM Analytics processes dates, it creates the following epoch date columns for each date processed:
            Epoch Time ColumnDescription
            <date_column_name>_sec_epoch For example, if the date column is CloseDate, the generated epoch second column is CloseDate_sec_epoch. This column provides the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT).
            <date_column_name>_day_epoch For example, if the date column is CloseDate, the generated epoch day column is CloseDate_day_epoch. This column provides the number of days that have elapsed since January 1, 1970 (midnight UTC/GMT).
            Example
            Example

            Let’s look at an example. You want to create an OppHistoryDelta dataset that contains opportunity history from the OpportunityHistory object and also calculates the deltas for opportunity amounts.

            The OpportunityHistory object contains the following data.

            OpportunityId CloseDate StageName Amount
            1 1/1/2014 New 100
            2 1/1/2014 New 100
            2 2/1/2014 ClosedWon 200
            1 3/1/2014 ClosedWon 100

            You create the following dataflow definition. 

            {
   "Extract_Opportunities": {
      "action": "sfdcDigest",
      "parameters": {
         "object": "OpportunityHistory",
         "fields": [
            { "name": "OpportunityId" },
            { "name": "CloseDate" },
            { "name": "StageName" },
            { "name": "Amount" }
         ]
      }
   },
   "Calculate_Delta": {
      "action": "delta",
      "parameters": {
         "dimension": "OpportunityId",
         "epoch": "CloseDate_day_epoch",
         "inputMeasure": "Amount",
         "outputMeasure": "DeltaAmount",
         "source": "Extract_Opportunities"
      }
   },
   "Register_Dataset": {
      "action": "sfdcRegister",
      "parameters": {
         "alias": "OppHistoryDelta",
         "name": "OppHistoryDelta",
         "source": "Calculate_Delta"
      }
   }
}

            To calculate the delta values for each opportunity amount, the delta transformation sorts the records by the dimension (text column) (OpportunityId) first, and then by time (CloseDate_day_epoch) as shown here.

            OpportunityID CloseDate StageName Amount
            1 1/1/2014 New 100
            1 3/1/2014 ClosedWon 100
            2 1/1/2014 New 100
            2 2/1/2014 ClosedWon 200

            After the records are sorted, for each dimension (text column) (OpportunityId), the transformation compares the previous value to the next value to determine the delta for each record. The transformation creates the following dataset.

            OpportunityId CloseDate StageName Amount DeltaAmount
            1 1/1/2014 New 100 0
            1 3/1/2014 ClosedWon 100 0
            2 1/1/2014 New 100 0
            2 2/1/2014 ClosedWon 200 100

            For the first record of each dimension (text column), the transformation inserts ‘0’ for the delta value.

            Note
            Note If an opportunity contains multiple changes on the same day, you must sort the records on a shorter time interval. In this case, sort on CloseDate_sec_epoch column. Otherwise, records aren’t sorted correctly, which means delta values are incorrect.
            • delta Parameters
              When you define a delta transformation, you set the action attribute to delta and specify the parameters.
             
            Loading
            Salesforce Help | Article