Loading
Salesforce Enforces New Security Requirements in Summer 2026Read More
CRM Analytics
Handle Null Numeric Values

You are here:

  1. Salesforce Help
  2. Docs
  3. CRM Analytics

Handle Null Numeric Values

When you create or update a dataset through your dataflow, recipe, or a CSV upload, for example, CRM Analytics replaces blank numeric values with the specified default value. When no default value is specified, CRM Analytics replaces blanks in numeric columns with 0 or null based on whether you enable null measure handling.

Note
Note Null measure handling is enabled in orgs created after the Spring ’17 release and can’t be disabled. To enable this feature in other orgs, see Enable Null Measure Handling in Orgs Created Before Spring ’17 .

CRM Analytics replaces blanks with:

  • 0 when null measure handling is disabled and no default value is specified
  • null when null measure handling is enabled and no default value is specified

This behavior is important because CRM Analytics treats null and 0 differently. They aren’t the same. Null means that no value exists. 0 represents a value. Math operations performed on null results in null. For example,

10 + null = null

To return 0 instead of null in a math operation, you can use an expression similar to this one: 10 + coalesce(null,0). For example, 10 + coalesce('Amount', 0), where Amount is null.

Aggregate functions—such as sum, average, count, min, and max—exclude null values from calculations. To understand how 0 and null are treated differently with aggregate functions, consider the following customer satisfaction scores, where one score is missing for Customer2.

CSAT dataset with nulls

When CRM Analytics replaces the blank value with a zero, the average and minimum calculations are incorrect. When the replacement is null, the average and minimum calculations are correct.

Null measure handling allows the use of null values in these dataflow transformations.

append CRM Analytics can append datasets with different measure columns. For example, one dataset has an Amount column that is not present in a second dataset. After the append, the new dataset has an Amount column containing nulls in rows from the second dataset. Without null measure handling, appending datasets is not possible when measure columns are different.
augment When the left key is null or has no match on the right, CRM Analytics inserts nulls in the columns added from the right. Without null measure handling, CRM Analytics inserts zeros for measures.
computeExpression

The defaultValue attribute for a computed column accepts a value of null. For example:

"SAQLNode": {
      "action": "computeExpression",
      "parameters": {
        "source": "extractOpp",
        "mergeWithSource": true,
        "computedFields": [
          {
            "name": "Amount2",
            "type": "Numeric",
            "precision": 18,
            "defaultValue": "null",
            "scale": 5,
            "saqlExpression": "Amount+Amount"
          }
computeRelative

The defaultValue attribute for a computed column accepts a value of null. When a value can’t be calculated, such as when previous() or next() has no value, CRM Analytics uses this default. For example:

"computeTrending": {
        "action": "computeRelative",
        "parameters": {
            "partitionBy": ["OpportunityId"],
            "orderBy": [
                {
                    "name":"CreatedDate", 
                    "direction":"asc"
                }
            ],
            "computedFields": [          
                {
                    "name": "AmountPrev",
                    "defaultValue" : "null",
                    "expression": {
                        "sourceField": "Amount",
                        "offset": "previous()",
                        "default": "null"            
                    }       
                }
dim2mea

The measureDefault parameter accepts a value of null. CRM Analytics uses this value when it can’t convert a dimension to a valid numeric value. When no default is specified, CRM Analytics uses a value of null. For example:

"Create_Measure_From_Dimension": {        
      "action": "dim2mea",        
      "parameters": {            
         "dimension": "StageVal__c",            
         "measure": "StageValue",            
         "measureDefault": "null",                      
         "source": "Extract_Opportunities"        
      }    
   }
sfdcDigest

The defaultValue attribute for a column accepts a value of null. When no default is specified, CRM Analytics uses a value of null. For example:

Sample Sfdc-digest :{    
   "Extract_Opportunities": {        
      "action": "sfdcDigest",        
      "parameters": {            
         "object": "Opportunity",
         "fields": [                
            {
               "name": "Amount",
               "defaultValue": "null",
               "precision":18
               "scale":2
            }
Update CRM Analytics can update non-null values with nulls.
Note
Note The delta transformation is not supported when null measure handling is enabled and dataflows containing delta transformations fail. To calculate changes in measure values over time, use computeRelative and computeExpression transformations instead. For an example, see Handle Null Numeric Values .
 
Loading
Salesforce Help | Article