Description
Retains the listed Dimensions on a Block and removes all others.
Syntax
source_block [KEEP [aggregator] [ON (<dimension1>[, <dimension2>])]: dimension3[, dimension4]]
source_blockThe Metric whose Dimensions you want to keep.aggregatorThe aggregation method applied to the removed Dimensions (e.g.SUM,AVG,FIRST,FIRSTNONBLANK). If omitted, the default isSUM.ONDefines the order in which the removed Dimensions are aggregated. It only applies to ordered aggregators (FIRST,LAST,FIRSTNONBLANK,LASTNONBLANK,FIRSTNONZERO,LASTNONZERO,TEXTLIST). When more than one Dimension is removed and you don't write anONexpression, Pigment adds it automatically so the order is explicit in the formula. You can then edit it to change the order.dimension1The Dimension you want to retain.dimension2Any additional Dimensions to retain, separated by a comma.
The default aggregator, if omitted, is SUM for objects of the Number data type. Other aggregators are described in the article Aggregation data in formulas.
Return type
same as source object
Examples
Revenue is a Metric of the Number data type, with the following Dimensions: Country, Product, and Month.
Case | Results |
|---|---|
| Returns the Revenue by Country where all other Dimensions were aggregated using sum. |
| Returns the Revenue by Month where all other Dimensions were aggregated using average. |
| Returns the Revenue by Country, taking the first non-blank value across the removed Dimensions. As |
| As above. You can set up formulas directly to specify the aggregation order. |
See also: REMOVE