This article describes the various allocation and aggregation options in Pigment, for use with modifiers and advanced aggregation.
For more information on aggregation and allocation use cases, see Use cases of aggregation and allocation.
Allocation Methods
Constant
This method takes the value from the source Metric or Property and applies it to every Item within the replacement Dimensions. This is the default behavior if no allocation method is defined.
Source_block[BY Constant: mapping attribute ]
Supported Data Types - All Types
Return Data Type - same as Input
Split
This method takes the value from the source Metric or Property and distributes it evenly across the Items within the replacement Dimensions.
Source_block[BY split: mapping attribute ]
Supported Data Types - Number, Integer
Return Data Type - Number
Aggregation Methods
Sum
This method returns the sum of the source values from the aggregated Dimension. This is the default behavior when using BY.
Source_block[BY: mapping attribute ]
Source_block[BY SUM: mapping attribute ]
Supported Data Types - Number, Integer
Return Data Type - same as Input
AVG
This method returns the average of the source values from the aggregated Dimension.
Source_block[BY AVG: mapping attribute ]
Supported Data Types - Number, Integer
Return Data Type - Number
MEDIAN
This method returns the median of the source values from the aggregated Dimension.
If the data has an odd number of observations, the middle number is returned (after arranging in ascending order)
If the data has an even number of observations, the average between the two center values is returned
Source_block[BY MEDIAN: mapping attribute ]
Supported Data Types - Number, Integer
Return Data Type - Number
STDEVS
This method returns the sample standard deviation of the source values from the aggregated Dimension.
Source_block[BY STDEVS: mapping attribute ]
Supported Data Types - Number, Integer
Return Data Type - Number
STDEVP
This method returns the population standard deviation of the source values from the aggregated Dimension.
Source_block[BY STDEVP: mapping attribute ]
Supported Data Types - Number, Integer
Return Data Type - Number
MIN
This method returns the minimum value of the source values from aggregated Dimension.
Source_block[BY MIN: mapping attribute ]
Supported Data Types - Number, Integer, Date
Return Data Type - same as Input
MAX
This method returns the maximum value of the source values from aggregated Dimension.
Source_block[BY MAX: mapping attribute ]
Supported Data Types - Number, Integer, Date
Return Data Type - same as Input
FIRSTNONBLANK
This method returns the first non blank value of the aggregated Dimension.
Source_block[BY FIRSTNONBLANK: mapping attribute ]
Supported Data Types - All Types
Return Data Type - same as Input
FIRSTNONZERO
This method returns the first non zero value of the aggregated Dimension.
Source_block[BY FIRSTNONZERO: mapping attribute ]
Supported Data Types - Number, Integer
Return Data Type - same as Input
FIRST
This method returns the first value of the aggregated Dimension.
Source_block[BY FIRST: mapping attribute ]
Supported Data Types - All Types
Return Data Type - same as Input
ℹ️ Note
Use of FIRST can add density to your Metric, slowing down performance.
In some cases, this performance loss can be improved by using FIRSTNONBLANK instead.
When you use FIRST, Pigment provides a Performance Hint in the formula bar. See Modeling Hints to find out more. To learn about the importance of sparsity, see Leverage sparsity for optimal performance.
LASTNONBLANK
This method returns the last non blank value of the aggregated Dimension.
Source_block[BY LASTNONBLANK: mapping attribute ]
Supported Data Types - All Types
Return Data Type - same as Input
LASTNONZERO
This method returns the last non zero value of the aggregated Dimension.
Source_block[BY LASTNONZERO: mapping attribute ]
Supported Data Types - Number, Integer
Return Data Type - same as Input
LAST
This method returns the last value of the aggregated Dimension.
Source_block[BY LASTBLANK: mapping attribute ]
Supported Data Types - All Types
Return Data Type - same as Input
ℹ️ Note
Use of LAST can add density to your Metric, slowing down performance.
In some cases, this performance loss can be improved by using LASTNONBLANK instead.
When you use LAST, Pigment provides a Performance Hint in the formula bar. See Modeling Hints to find out more. To learn about the importance of sparsity, see Leverage sparsity for optimal performance.
ANY
Returns TRUE if at least one aggregated item is TRUE, else FALSE.
Source_block[BY ANY: mapping attribute ]
Supported Data Types - Boolean
Return Data Type - same as Input
ALL
Returns TRUE if all aggregated items are TRUE or BLANK, else FALSE.
If all aggregated items are BLANK, then the BY ALL result will also be BLANK. Because Pigment’s engine is sparse, blank values are not stored in the database.
Source_block[BY ALL: mapping attribute ]
Supported Data Types - Boolean
Return Data Type - same as Input
COUNT
Returns the number of aggregated items (BLANK cells are not included).
Source_block[BY COUNT: mapping attribute ]
Supported Data Types - All types
Return Data Type - Integer
COUNTBLANK
Returns the number of BLANK items in the aggregated dimension.
Source_block[BY COUNTBLANK: mapping attribute ]
Supported Data Types - All types
Return Data Type - Integer
COUNTALL
Returns the number of aggregated items (BLANK cells are included)
Source_block[BY COUNTALL: mapping attribute ]
Supported Data Types - All types
Return Data Type - Integer
COUNTUNIQUE
Returns the number of unique values in the aggregated dimension (BLANKS not included)
Source_block[BY COUNTUNIQUE: mapping attribute ]
Supported Data Types - All types
Return Data Type - Integer
TEXTLIST
Returns the list of aggregated text values, separated by a comma.
Source_block[BY TEXTLIST: mapping attribute ]
Supported Data Types - Text
Return Data Type - same as Input