Pigment is a very powerful multi-dimensional tool and one of the reasons why is the way it can handle dimensions in formulas. To do this in Pigment, we use modifiers. Before going forward, make sure you already know all the different modifiers that can be used to change the Dimensions of formula items and how they work.
Table of Contents
List of modifiers available in Pigment
MODIFIER | DEFINITION |
---|---|
ADD | To add a new Dimension to a formula item. |
REMOVE | To remove a Dimension to a formula item. |
BY | To replace Dimensions of a formula item based on a mapping Property or Metric. This would result either in aggregating or allocating data. |
SELECT | To remove a Dimension to a formula item while applying a filter at the same time. |
FILTER | To filter on one (or several) items of a Dimension. The FILTER modifier does not change the Dimensions of the formula items. |
With these modifiers, you can handle all the transformations you need to manipulate your data. But did you know that you have the possibility of chaining modifiers? By chaining modifiers, you can work with different dimensionalities within the same formula and it can save you some unnecessary block creations.
As you already know, modifications are performed within o]
in Pigment. There are two ways of chaining them:
Option 1: Keep each modifier within its own brackets and concatenate them.
'Your Metric'>modification 1]rmodification 2]
Option 2: Separate each modifier with a ";
" within the same brackets.
'Your Metric'pmodification 1; modification 2]
Note
For both of these options, the order matters: modification 1 is applied, then modification 2, and so on.
Example 1
Imagine we have a Metric Region Target
with the Region Dimension.
We want to manipulate these targets with the following steps:
- Filter the
Region Target
Metric on EMEA only - Allocate
EMEA Target
on corresponding EMEA Countries - Allocate
EMEA countries target
on the Month Dimension
Without chaining Dimensions we would require 3 additional Metrics to do these manipulations.
EMEA target
Metric with the filter:
EMEA Countries target
with a first allocation on the Country dimension:
Monthly EMEA countries target
with a second allocation on the Month Dimension:
Note
This is works well, but you could do all these steps in one single formula with a single Metric!
- With Option 1:
- Or with Option 2:
Example 2
By chaining Dimensions, you can write formulas that apply at different dimensionalities and reach a highly detailed level of customization.
Here is another example:
('Base Value Region' add constant : Month] * 'Driver by Region & Month')cby constant: Country.Region]yadd split: Product]
Here is what happens there:
- We have a Metric
Base Value Region
with the Region Dimension
- We allocate this metric on the Month Dimension
- We multiply it by another Metric which is already defined on the Region and Month Dimensions
- We allocate the result of this multiplication on the Country and on the Product Dimensions
Special case: Chaining the same modifier
Now that we have seen that we could chain different modifiers, you might be asking yourself what happens when you chain the same modifier.
For example, you will notice that writing this formula:
'Region Target'2Add constant : Month, Product]
is the same as writing this one:
'Region Target'/Add constant : Month] Add constant: Product]
So usually, you will prefer to write the first one (which is shorter).
However, on some occasions, these two ways of writing the formula are not equivalent. That is the case, for instance, when you are using non-commutative aggregators like AVG
, FIRSTNONBLANK
or LASTNONBLANK
in a Remove
or By
aggregators.
Example with
Let's take this Metric as an example:
Here is what you get when using the following formula:
'Revenue per Ctry & Product' by avg : Country.Region, Month.Quarter]
And here is the result of the second formula, and in this case, order matters:
'Revenue per Ctry & Product'aby avg : Country.Region]rby avg: Month.Quarter]
Changing the aggregation order:
'Revenue per Ctry & Product'
In this case, because there are some blank cells in the source Metric, the average of all values is not the same as the average of averages, and the order matters. Of course, it would not have been the case had all cells been defined with a value.
So pay attention when chaining your modifiers!
Note
Excel equivalent: none
See also
For detailed insights and video discussions on typical use cases and common pitfalls of each modifier by a Pigment Solution Architect, explore the Write and Test Formulas Learning Path in the Academy.
Let's take this Metric as an example:
Here is what you get when using the following formula:
'Revenue per Ctry & Product' by avg : Country.Region, Month.Quarter]
And here is the result of the second formula, and in this case, order matters:
'Revenue per Ctry & Product'aby avg : Country.Region]rby avg: Month.Quarter]
Changing the aggregation order:
'Revenue per Ctry & Product'
In this case, because there are some blank cells in the source Metric, the average of all values is not the same as the average of averages, and the order matters. Of course, it would not have been the case had all cells been defined with a value.
So pay attention when chaining your modifiers!
Note
Excel equivalent: none
See also
For detailed insights and video discussions on typical use cases and common pitfalls of each modifier by a Pigment Solution Architect, explore the Write and Test Formulas Learning Path in the Academy.