By mapping parameter -> "Arrow"

  • 14 September 2022
  • 2 replies
  • 2493 views

Userlevel 7
Badge +13

Description

Works with BY modifier to point to a mapping metric to transform one or more dimensions depending on syntax and mapping metric. You can control which dimensions are replaced or added by adjusting the syntax. This is function is sometimes referred to as “the arrow”.

 

Syntax

Syntax is critical when using this parameter as it can change the way the modifier works. Source_metric_dimension, while optional, this parameter defines which dimensions are replaced.

 

Source_metric[BY Aggregation method : Source_metric_dimension 1, Source_metric_dimension 2 -> mapping metric]

  • Source_metric is the metric that contains the values to be aggregated or allocated.
  • Allocation method (optional) 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 (optional) is where you define the way that values are aggregated. If it is not defined, values will be aggregated using the SUM method.
  • Source_metric_dimension (optional) are the dimensions of the source metric to be replaced. The dimension will be removed. If you are replacing more than one dimension, a comma is needed between them. 
  • mapping metric is a metric that defines the relationship between the dimensions in the Source_metric and the dimensions in the target metric. The mapping metric must have a dimension data type.

 

Return type

same as source metric

 

Table of Contents 

 

What is a mapping metric?

A mapping metric is used to define a relationship between multiple lists. Similar to when using a mapping attribute a mapping metric must be a dimension data type. The mapping metric’s dimension type must be set to the dimension you want to aggregate or allocate data across.  For example, if you wanted to aggregate sales across a Team dimension, your data type for your mapping metric would be Team.

 

Source_metric_dimension

The source metric dimension parameter is optional and defines which, if any, dimensions should be replaced by the mapping metric. This parameter must be defined by a dimension used in the source metric.  You can use one or more dimensions in this parameter. The dimensions used here must also be present in the mapping metric. For example, if you wanted to replace a Country dimension by a Team dimension, your source and mapping metric must use Country in the metric structure and your data type would be set as Team

dimension.    

 

Here is an example of one dimension being replaced.  

 Source_metric[BY: Country -> mapping metric] 

In this example,  Country is replaced with Team .  The mapping metric is located in the top left of this board.  Because the mapping metric is dimensionalized by Products and County, you can replace either of these dimensions with Team.   When reading this formula, because the dimension is before the → it will be replaced by the dimension used as the data type withing the mapping metric.

Click on the image to make it larger.  If you look at Product B, you’ll notice it gets Aggregated by Team A and Team B.  Because there is no mapping with Team C there is no data aggregated to it. 

 

 

Using the same Mapping and Source metric as above, you can also replace Product with Team by adjusting the formula.

Source_metric[BY: Product -> mapping metric]

Click on the image to make it larger.  If you look at France, you’ll notice it gets Aggregated by Team A and Team C.  Because there is no mapping with Team B there is no data aggregated to it. 

 

 

Adding a dimension with → 

 

If you use the -> but no dimension in the Source_metric_dimension, it will add the dimensions from the mapping, but won't aggregate the data. The added dimension could be seen like a classification:  For example, what is the Team in charge for each Product in a Country.
I can also act as a filter. For example. if no Team is in charge, the cell from the source metric won't be reported in the formula result.

 

You can use → with the BY modifier to bring in an additional dimension to your analysis. In this case we will remove the optional  Source_metric_dimension and your formula would beBY:> mapping metric]  .  When you move this method the data will allocate using the CONSTANT method.  This means that it will add the same value across every newly created intersection.  

 

Here is an example where the formula is Source_metric[BY: -> mapping metric]

The Source_metric_dimenision parameter is left out and therefore we will add in the dimension from the mapping metric. In this example,  the mapping metric is set to a data type of  Team .  The mapping metric is located in the top left of this board.  The mapping metric is dimensionalized by Products and County, which are both present in the source metric.  This allows it to add the Team dimension to our source metric.  In the resulting metric and the source metric, Months has been added as a page selector for easier visibility. 

 

 

Methods when adding a dimension 

 

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 when adding a dimension using the ->. 

Source_block[BY: → mapping metric]

Source_block[BY CONSTANT: → mapping metric]

 

 

 

Aggregation Methods when replacing one or more dimensions

 

SUM 

This method returns the sum the source values from aggregated dimension. This is the default behavior when using BY and ->.

Source_block[BY: Source_metric_dimension 1 -> mapping attribute]

Source_block[BY SUM: Source_metric_dimension 1 -> mapping attribute]

Supported Data Types - Number, Integer 

 

AVG

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

Source_metric[BY AVG: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - Number, Integer 

 

MIN

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

Source_metric[BY MIN: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - Number, Integer, Date

 

MAX

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

Source_metric[BY MAX: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - Number, Integer, Date

 

FIRSTNONBLANK

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

Source_metric[BY FIRSTNONBLANK: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - All Types 

 

FIRSTNONZERO

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

Source_metric[BY FIRSTNONZERO: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - Number, Integer

 

FIRST

This method returns the first value of the aggregated dimension.

Source_metric[BY FIRST: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - All Types

 

LASTNONBLANK

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

Source_metric[BY LASTNONBLANK: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - All Types 

 

LASTNONZERO

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

Source_metric[BY LASTNONZERO: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - Number, Integer

 

LAST

This method returns the last value of the aggregated dimension.

Source_metric[BY LAST: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - All Types 

 

ANY

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

Source_metric[BY ANY: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - Boolean 

 

ALL

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

Source_metric[BY ALL: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - Boolean 

 

COUNT

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

Source_metric[BY COUNT: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - All types

 

COUNTBLANK

Returns the number of BLANK items in the aggregated dimension.

Source_metric[BY COUNTBLANK: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - All types

 

COUNTALL

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

Source_metric[BY COUNTALL: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - All types

 

COUNTUNIQUE

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

Source_metric[BY COUNTUNIQUE: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - All types

 

TEXTLIST

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

Source_metric[BY TEXTLIST: Source_metric_dimension 1 -> mapping metric]

Supported Data Types - Text

 

Excel equivalent: none

See also: BY


This topic has been closed for comments

2 replies

Badge

Will the use of SPLIT as an allocation method work as the opposite of the CONSTANT allocation method? I see CONSTANT is documented and SPLIT is not mentioned.

My use case is to plan at a higher level of the chart of account structure, but when sending my plan data back to the data hub, I’d like to have the plan data at the base GL account level.

Userlevel 6
Badge +11

Will the use of SPLIT as an allocation method work as the opposite of the CONSTANT allocation method? I see CONSTANT is documented and SPLIT is not mentioned.

My use case is to plan at a higher level of the chart of account structure, but when sending my plan data back to the data hub, I’d like to have the plan data at the base GL account level.

yes, split would do a simple division by the count of “children”.