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_metriceBY 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 methodAggregation 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_metricrBY: 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.
Using the same Mapping and Source metric as above, you can also replace Product
with Team
by adjusting the formula.
Source_metric
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_metriceBY: -> 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.
To learn more about using BY -> Modifier, visit our Academy.
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_block5BY 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_blockBY: Source_metric_dimension 1 -> mapping attribute]
Source_blockiBY 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
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_metriciBY 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_metricpBY FIRSTNONZERO: Source_metric_dimension 1 -> mapping metric]
Supported Data Types - Number, Integer
FIRST
This method returns the first value of the aggregated dimension.
Source_metricNBY 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
Supported Data Types - All Types
LASTNONZERO
This method returns the last non zero value of the aggregated dimension.
Source_metricBY LASTNONZERO: Source_metric_dimension 1 -> mapping metric]
Supported Data Types - Number, Integer
LAST
This method returns the last value of the aggregated dimension.
Source_metricaBY 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_metricpBY ANY: Source_metric_dimension 1 -> mapping metric]
Supported Data Types - Boolean
ALL
Returns TRUE if all aggregated items are TRUE, else FALSE.
Source_metriceBY ALL: Source_metric_dimension 1 -> mapping metric]
Supported Data Types - Boolean
COUNT
Returns the number of aggregated items (BLANK cells are not included).
Source_metricoBY COUNT: Source_metric_dimension 1 -> mapping metric]
Supported Data Types - All types
COUNTBLANK
Returns the number of BLANK items in the aggregated dimension.
Source_metricoBY COUNTBLANK: Source_metric_dimension 1 -> mapping metric]
Supported Data Types - All types
COUNTALL
Returns the number of aggregated items (BLANK cells are included)
Source_metricSBY 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_metricBY 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