In Pigment, every Dimension List is ordered. The default order of a list is the creation order of each Dimension List item. However, you can change this default order in the Dimension List setting. In the Special properties menu, there is a Ranking property field where you can select any unique properties as the ranking property.
This default ranking is used whenever order matters and especially when you perform an aggregation (using a By
, Remove
, or Select
modifier) with an aggregator that uses the list order.
For example, the first
aggregator may return a different value if you change the ranking property because the list order has changed.
'My Source Metric'[Remove first : 'Source Metric Dimension']
Please find below the list of aggregators where the ranking of a dimension impacts the result:
Aggregator | Description | Available data types |
---|---|---|
First | Returns the value of the first item from the aggregated dimension. | All |
Firstnonblank | Returns the value of the first non-blank item from the aggregated dimension. | All |
Firstnonzero | Returns the value of the first item that is different from zero from the aggregated dimension. | Number, Integer |
Last | Returns the value of the last item from the aggregated dimension. | All |
Lastnonblank | Returns the value of the last non-blank item from the aggregated dimension. | All |
Lastnonzero | Returns the value of the last item that is different from zero from the aggregated dimension. | Number, Integer |
Textlist | Returns the list of aggregated text values, separated by a comma. | Text |
So, if you change the list ranking properties, aggregating data using the above aggregators will return different results.
Example
Here is a country list :
When no ranking property is defined, the first item is Italy
.
When we use the Order
property as the ranking property, the first item is France
.
However, you can also choose any unique property to perform your ordered aggregation, not necessarily the one defined in the Metric's settings! To do this, use the on
operator.
And you can even use the ranking property of one of your Dimension's properties!