Pigment has a sparse engine, meaning that it can handle blank cells. This article sets out how blanks are treated in formulas.
 

What is sparsity and why is Pigment’s engine sparse?

A sparse engine means that empty values are not filled by default with a 0 or a FALSE: your datasets can include blanks. This allows for much faster computation than in a non-sparse engine.

Implications of sparsity

Pigment has its own heuristic governing how simple operators should behave in a sparse context. They behave differently depending on whether the datasets on each side of the operators have the same Dimensions or not. There are two main types of behavior:

• one for addition, subtraction and OR operators
• one for multiplication, division and AND operators 

Formulas featuring Metrics with the same Dimensions

In the examples below, the input Metrics and the output Metric all have Product and Country as Dimensions. The behavior depends on the operator:

• Addition/subtraction/OR:

  • The output Metric has the same Dimensions as the input Metrics.
  • When a blank value is combined with a numeric value, the result is always the numeric value. 

   

• Multiplication/Division/AND:

  • The output Metric has the same Dimensions as the input Metrics. 
  • When a blank value is combined with a numeric value, the result is always blank.

Formulas featuring Metrics with one common Dimension

When the Metrics have a Dimension in common, but one Metric has an additional Dimension, the resulting Metric has both Dimensions.

In the examples below, the first Metric has both Country and Product, while the second Metric only has Product. The output has both Country and Product. The behavior depends on the operator:

• Addition/subtraction/OR:

  • The blank values of the Metric with multiple Dimensions remain blank in the output Metric.

• Multiplication/division/AND:

  • The blank values in either Metric remain blank in the output Metric.  

The same happens when a constant or scalar is introduced. Below, the first Metric has both Country and Product, while the second Metric only has Product. The output has both Country and Product. The behavior depends on the operator:

• Addition/subtraction/OR:

  • The blank values of the Metric with multiple Dimensions remain blank in the output Metric.
     

• Multiplication/division/AND:

  • The blank values in either Metric remain blank in the output Metric.  
     

Formulas featuring Metrics with no common Dimensions

When the Metrics have no common Dimensions, the resulting Metric has all their Dimensions. In the examples below, the first Metric has Product, while the second Metric has Country. The output has both Country and Product. The behavior is the same for either group of operators:

• Addition/subtraction/OR:

  • The blank values in either Metric remain blank in the output Metric.  
     

• Multiplication/division/AND:

  • The blank values in either Metric remain blank in the output Metric.  

Formulas featuring a Metric and a constant

When a formula features a Metric and a constant or scalar, the blank values in the input Metric remain blank in the output Metric. The behavior is the same for either group of operators:

• Addition/subtraction/OR:
   

• Multiplication/division/AND:
   

Blank cells in the Boolean data type

Most of this article deals with blanks in Metrics of type number. But blanks also exist in Booleans. A Boolean that has not been used is considered blank. Pigment distinguishes between blank and false Booleans by graying out blanks. See below the different appearances a Boolean can have. 
 

Learn more

• Filter Data in Pigment
• Add modifier
• Compare Blocks using Operators