Effects of Grouping on Fields that include Nulls in Wave Analytics
|Knowledge Article Number||000239982|
|Description||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, details here.
If a record does not have a value (is null) in that field, it will not be included in the results. This can result in perceived discrepancies in row counts, sums, or other calculations.
For example, the following dataset has a null in the 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 gives us Sum of Value = 40. The record with the null field is excluded from calculations by the Grouping.
|Resolution||This can be addressed in several ways:
Add a defaultValue to dataflow or schema
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.
Use ungrouped lens for calculations on dashboards
If displaying 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.