---
schema_version: "1"
id: a1392f5130689fae68e6c8d87dca9ff2e88523c4eb84c79745b49712a93a50e4
title: Handle Null Numeric Values
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_null_measures.md
canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_null_measures.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: 7fa6b33f0ead8da58414fcb85f09cdfc51535b3b3bebf27de99ac6c91195edf5
description: 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.
products:
  - CRM Analytics
  - Cross Cloud
release: "264"
---
# 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](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 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](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_enable_null_measures.htm&type=5&language=en_US) .

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](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/bi/images/bi/bi_integrate_null_measures_csat_nulls_and_zeros.png)

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.

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small ">
      <colgroup>
        <col style="width:15.503875968992247%">
        <col style="width:84.49612403100775%">
      </colgroup>
      <tbody>
        <tr class="">
          <td class="slds-cell-wrap" style="vertical-align:top;">append</td>

          <td class="slds-cell-wrap" style="vertical-align:top;">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.</td>

        </tr>
        <tr class="">
          <td class="slds-cell-wrap" style="vertical-align:top;">augment</td>

          <td class="slds-cell-wrap" style="vertical-align:top;">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.</td>

        </tr>
        <tr class="">
          <td class="slds-cell-wrap" style="vertical-align:top;">computeExpression </td>

          <td class="slds-cell-wrap" style="vertical-align:top;">
            <p>The <code>defaultValue</code> attribute for a computed column accepts a value
              of null. For example:</p>
            <pre class="line-numbers slds-box slds-box_x-small slds-theme_shade slds-m-top_medium slds-m-bottom_medium slds-scrollable_x"><code>"SAQLNode": {
  "action": "computeExpression",
  "parameters": {
    "source": "extractOpp",
    "mergeWithSource": true,
    "computedFields": [
      {
        "name": "Amount2",
        "type": "Numeric",
        "precision": 18,
        "defaultValue": "null",
        "scale": 5,
        "saqlExpression": "Amount+Amount"
      }</code></pre>
          </td>

        </tr>
        <tr class="">
          <td class="slds-cell-wrap" style="vertical-align:top;">computeRelative</td>

          <td class="slds-cell-wrap" style="vertical-align:top;">
            <p>The <code>defaultValue</code> attribute for a computed column accepts a value
              of null. When a value can’t be calculated, such as when <code>previous()</code> or
              <code>next()</code> has no value, CRM Analytics uses this default. For
              example:
            </p>
            <pre class="line-numbers slds-box slds-box_x-small slds-theme_shade slds-m-top_medium slds-m-bottom_medium slds-scrollable_x"><code>"computeTrending": {
   "action": "computeRelative",
   "parameters": {
      "partitionBy": ["OpportunityId"],
      "orderBy": [
        {
          "name":"CreatedDate", 
          "direction":"asc"
        }
       ],
       "computedFields": [          
         {
           "name": "AmountPrev",
           "defaultValue" : "null",
           "expression": {
             "sourceField": "Amount",
             "offset": "previous()",
             "default": "null"            
          }       
        }</code></pre>
          </td>

        </tr>
        <tr class="">
          <td class="slds-cell-wrap" style="vertical-align:top;">dim2mea</td>

          <td class="slds-cell-wrap" style="vertical-align:top;">
            <p>The <code>measureDefault</code> 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:</p>
            <pre class="line-numbers slds-box slds-box_x-small slds-theme_shade slds-m-top_medium slds-m-bottom_medium slds-scrollable_x"><code>"Create_Measure_From_Dimension": {        
   "action": "dim2mea",        
   "parameters": {            
      "dimension": "StageVal__c",            
      "measure": "StageValue",            
      "measureDefault": "null",                      
      "source": "Extract_Opportunities"        
   }    
}</code></pre>
          </td>

        </tr>
        <tr class="">
          <td class="slds-cell-wrap" style="vertical-align:top;">sfdcDigest</td>

          <td class="slds-cell-wrap" style="vertical-align:top;">
            <p>The <code>defaultValue</code> attribute for a
              column accepts a value of null. When no default is specified, CRM
              Analytics uses a value of null. For
              example:</p>
            <pre class="line-numbers slds-box slds-box_x-small slds-theme_shade slds-m-top_medium slds-m-bottom_medium slds-scrollable_x"><code>Sample Sfdc-digest :{    
   "Extract_Opportunities": {        
      "action": "sfdcDigest",        
      "parameters": {            
         "object": "Opportunity",
         "fields": [                
            {
               "name": "Amount",
               "defaultValue": "null",
               "precision":18
               "scale":2
            }</code></pre>
          </td>

        </tr>
        <tr class="">
          <td class="slds-cell-wrap" style="vertical-align:top;">Update</td>

          <td class="slds-cell-wrap" style="vertical-align:top;">CRM Analytics can update non-null values with nulls.</td>

        </tr>
      </tbody>
    </table>

![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 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 examples, see [computeRelative](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_computerelative_transformation.htm&language=en_US) and [computeExpression](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_saql_transformation.htm&language=en_US).