Loading

Grouped Field has incorrect row count, sum, or calculation in CRM Analytics

Date de publication: Aug 3, 2023
Description

Note: Many of the details below also apply to sorting functionality in CRM Analytics. For additional details, see Limitations of Null Values in Dimensions in CRM Analytics.

When exploring data in a Lens, you can Group by a field to break down the results by the values in that field. Grouping can also be performed in SAQL. For additional details, see our Developer Documentation.

If a record does not have a value in that field (is null), it will not be included in the results. This can result in perceived discrepancies in row counts, sums, or other calculations.

 

Example 

The following dataset has a null contained within Region:

ID, Region, Value
1, NW,10
2,,5
3,NE,10
4,SE,10
5,SW,10

Without grouping, the Sum of Value would display the following: 
Sum of Value = 45

When grouped on Region, the Sum of Value would display the following: 
NE, Sum of Value = 10
NW, Sum of Value = 10
SE, Sum of Value = 10
SW, Sum of Value = 10

Totaling the Grouped Lens results in a Sum of Value which equals 40. The record with the null field is excluded from calculations by the Grouping.
Résolution

Add a defaultValue to dataflow or schema

a) Use the defaultValue override in the dataflow sfdcDigest transformation or external data schema to specify a value for the field in records where it is null
b) You can also use the coalesce() function to replace nulls with a default value 

Example:

  • q = load "dataset";
  • q = group q by 'Year';
  • q = foreach q generate 'Year', coalesce(sum(Amount)/sum(Quantity),0) as 'AvgPrice';
 

Use an ungrouped lens for calculations on dashboards

If you display the value on a dashboard, you can include a widget powered by an ungrouped version of the lens. This will allow the widget calculations to display the value, including the null records. Faceting will allow the calculations to adjust if filtering is applied.
Numéro d’article de la base de connaissances

000382902

 
Chargement
Salesforce Help | Article