By mapping parameter -> "Arrow"

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 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_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.

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

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_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_block�BY: 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_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_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_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


        
                
                            
            
            
                                                                                                                                    
                            
                                                    
                                Page 1 / 1 
                            
                                                                            
    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.
            
    
                                                    
    		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”.



            
                                      
                                                                                                
            


Ask your question here!

 Not finding what you're looking for?
 Don't be shy - let the Community help.
Ask your question here!



    

                                        
                                                        
            
        
    



                                    Powered by Gainsight
Terms & ConditionsCookie settings

            
        


        
            
    
        
    
        

                                    
                

    
    
      
    




            Sign up
    

    Already have an account? Login
    



    
            Single Sign-On Need help?
 
    
    
    
    
    
    
            
            Log in with your Pigment account
        
    
    
    
    
    
    
    
    
    



            
        
                            
                
    
        
    

            Log into the Pigment Community
    
    
    
            Single Sign-On Need help?
 
    
    
    
    
    
    
            
            Log in with your Pigment account
        
    
    
    
    
    
    
    
    
    
                
        
                
            Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.



  
      
  

      
    
        
                        
                            Enter your e-mail address
            
            
                                
        
        
                        

        
    

  
      
                Back to overview
        
            


        
    
                

        
    
          
        Scanning file for viruses.
        Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
        OK
      

      
        This file cannot be downloaded
        Sorry, our virus scanner detected that this file isn't safe to download.
        OK