Description

Aggregates or allocates a Block's data depending on the Dimensions given.

Returns a block(Metric or Property) with different Dimension.  Pigment’s BY modifier can be used to both aggregate or allocate data depending on the relationship between the dimensions used in the formula. 

Syntax

Syntax is similar when using for allocation or aggregation, the difference is when defining the method.

source_blockrBY allocation_method: mapping attribute 1, imapping_attribute_2]]

or

source_blockrBY aggregation_method: mapping attribute 1, imapping_attribute_2]]

Source_blockuBY allocation method: mapping attribute 1, mapping attribute 2]

• Source_block is the metric or list property that contains the values to be aggregated or allocated 

• allocation method is where you define the way that values are distributed, this is an optional field. If it is not defined, values will be distributed by the CONSTANT method

• aggregation method is where you define the way that values are aggregated, this is an optional field. If it is not defined, values will be aggregated using the SUM method.

• mapping_attribute_1 is a list property or metric that defines the relationship between the dimensions in the Source_block and the dimensions in the target metric. The mapping attribute must have a dimension data type.  You must have 1 mapping attribute per use of the BY modifier. 

• mapping_attribute_2 is optional. This can be used when aggregating or allocation across multiple dimensions.

Return type

same as source object

Table of Contents 

What is a mapping attribute?

A mapping attribute is used to define a relationship between two lists. It can be either a property on one of the list or a metric using one of the list.  The critical element is that the property or metric has to be a dimension data type.   

For example, if you have a country and a region dimension.  You could use a mapping attribute to define how countries roll up to regions.  In order to do this, you must create a property or metric that is set to the dimension data type.  Here you can see an example from a Country list.  Because there are multiple countries within each region, you would set up the mapping property on the Country list.

How do you set up allocation vs aggregation using BY?

The determining factor on if data is allocated or aggregated when using the BY modifier is the relationship established in the mapping attribute. One relationship type is called One-to-Many (displayed as 1->N).  This is where you have the value of one item that you want allocated across multiple items. The other relationship type is Many-to-One (displayed as N ->1). In this relationship, you have many items that are aggregated into one item. 

One-to-Many

One-to-Many relationships are established when one item has a connection to many items in the other list.  For example, one region, Europe, Middle East, Africa, (EMEA), has a relationship to many items in the country list, France, Spain, United Kingdom and Italy.  If my source data is at the region level and I want to display it at the country level, that would be the One-to-Many relationship. This is because each region’s data would be distributed to many countries. In this example, the BY modifier would use an Allocation method.

Many-to-One

Many-to-One relationships are defined by multiple items being aggregated into one item. Let’s use the same example as above, where we have multiple countries rolling up to one region. If my source data is at the Country level and I want to display it at the Region level, this would be a Many-to-One relationship.  In this example, the BY Modifier would use the Aggregation method. It would take the values from France, Spain, United Kingdom and Italy and aggregate them into the EMEA region.  

The BY modifier will either allocate or aggregate based upon how we are trying to transform the data.  If your source data is at the Country level and your formula is at the Region level, the BY modifier will aggregate those values. If your source data at the Region level and you want to calculate it at the country level, it will allocate the values. The default behavior for allocation is CONSTANT and the default behavior for aggregation is SUM. There are other methods below that you can use as long as you define them in the syntax. 

Allocation Methods 

Constant 

This method will take the value from the source metric or property and apply it to every item within the replacement dimensions. This is the default behavior if no allocation method is defined. 

Source_blocksBY Constant: mapping attribute ]

Supported Data Types - All Types 

Split 

This method will take the value from the source metric or property and distribute it evenly across the items within the replacement dimensions.

Source_blockiBY split: mapping attribute ]

Supported Data Types - Number, Integer 

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_blockeBY: mapping attribute ]

Source_blocknBY SUM: mapping attribute ]

Supported Data Types - Number, Integer 

AVG

This method returns the average of the source values from aggregated dimension.

Source_block;BY AVG: mapping attribute ]

Supported Data Types - Number, Integer 

MIN

This method returns the minimum value of the source values from aggregated dimension.

Source_blocktBY MIN: mapping attribute ]

Supported Data Types - Number, Integer, Date

MAX

This method returns the maximum value of the source values from aggregated dimension.

Source_blockeBY MAX: mapping attribute ]

Supported Data Types - Number, Integer, Date

FIRSTNONBLANK

This method returns the first non blank value of the aggregated dimension.

Source_blockrBY FIRSTNONBLANK: mapping attribute ]

Supported Data Types - All Types 

FIRSTNONZERO

This method returns the first non zero value of the aggregated dimension.

Source_blockoBY FIRSTNONZERO: mapping attribute ]

Supported Data Types - Number, Integer

FIRST

This method returns the first value of the aggregated dimension.

Source_blocknBY FIRST: mapping attribute ]

Supported Data Types - All Types

LASTNONBLANK

This method returns the last non blank value of the aggregated dimension.

Source_blocktBY LASTNONBLANK: mapping attribute ]

Supported Data Types - All Types 

LASTNONZERO

This method returns the last non zero value of the aggregated dimension.

Source_blockrBY LASTNONZERO: mapping attribute ]

Supported Data Types - Number, Integer

LAST

This method returns the last value of the aggregated dimension.

Source_blockoBY LASTBLANK: mapping attribute ]

Supported Data Types - All Types 

ANY

Returns TRUE if at least one aggregated item is TRUE, else FALSE.

Source_blocktBY ANY: mapping attribute ]

Supported Data Types - Boolean 

ALL

Returns TRUE if all aggregated items are TRUE or BLANK, else FALSE.

Source_blockuBY ALL: mapping attribute ]

Supported Data Types - Boolean 

COUNT

Returns the number of aggregated items (BLANK cells are not included).

Source_blocknBY COUNT: mapping attribute ]

Supported Data Types - All types

COUNTBLANK

Returns the number of BLANK items in the aggregated dimension.

Source_blocktBY COUNTBLANK: mapping attribute ]

Supported Data Types - All types

COUNTALL

Returns the number of aggregated items (BLANK cells are included)

Source_blockaBY COUNTALL: mapping attribute ]

Supported Data Types - All types

COUNTUNIQUE

Returns the number of unique values in the aggregated dimension (BLANKS not included)

Source_block0BY COUNTUNIQUE: mapping attribute ]

Supported Data Types - All types

TEXTLIST

Returns the list of aggregated text values, separated by a comma.

Source_blocktBY TEXTLIST: mapping attribute ]

Supported Data Types - Text

Excel equivalent: VLOOKUP or SUMIF

See also: REMOVE