Pigment provides powerful operators to perform complex aggregations and allocations. We call them dimension modifiers because they will modify the dimensionality of objects.
For both aggregations and allocations, we decided to use the same magical keyword
BY. It is simple to remember since we usually say that we:
aggregate data by a dimension (country revenue aggregated by region, employee by team, etc.)
allocate data by a dimension (region target allocated by country, grade salary by TBH, annual target by month, etc.)
In this specific page, we will focus on aggregations Σ.
Grouping Views or Formula Aggregation?
Even if you can perform aggregations in Views by grouping data like in a Pivot Table in Excel, you will also need at some point to aggregate data stored in transactional lists or metrics to match the granularity of data in other metrics.
Most of the examples below would be equivalent to the SUMIF or AVERAGEIF functions in Excel. But Pigment provides other aggregation methods not covered in a single function by Excel.
Aggregating data from a List
Let's say you store a transactions list called
Orders in which you find columns such as:
Now you may want to create a metric called
Orders Revenue to aggregate the
Orders data by
Month, to pivot the dimensions and to include them in other calculations (like the calculation of a Gross Margin).
This metric would be set with the type number and the desired dimensions (
Month). It's formula would be:
Orders.Amount[BY SUM: Orders.Customers, Orders.Product, Orders.Month]
Which can be read as : in the list
Orders, take the property
Amount and SUM it BY the
In this example, you see the method of aggregation just after the BY, [BY SUM: ... ]. If this method not specified for metric of type number, Pigment applies a SUM by default.
Other aggregation methods are :
For number and integer: SUM, AVG, MIN, MAX, FIRSTNONBLANK (returns the value of the first cell non blank) FIRSTNONZERO (returns the value of the first cell different than 0)
For date: MIN, MAX, FIRSTNONBLANK
For boolean: ANY, ALL, FIRSTNONBLANK
For text and dimension: FIRSTNONBLANK
How to count?
For all types above you can count values:
COUNT to count all non blank cells,
COUNTBLANK to count only blank cells,
COUNTALL to count all values, even blank/empty cells)
COUNTUNIQUE to count only distinct values (and not blank)
The resulting metric must be set as number or integer.
Aggregating data from metrics
Aggregation of a metric's data works the same way but instead of referencing the list, you need to the metric name.
We may want to create a metric called
Category Revenue that stores the data from above by Product Category.
'Orders Revenue'[BY SUM: 'Product'.'Category']
Which can be read as : using the data from the metric
Orders Revenue, return the SUM BY the property
Category of the list