BY Clause Variations

In its basic implementation, the BY clause stops breaking down report values by anything smaller than the referenced attribute level. In this section, you can explore additional BY statements, which enable variations on aggregation functionality.

This section includes examples of the BY metric constructs, which you can create in the Advanced Metric Editor.

The referenced project is the GoodData Sales Analytics App demo project.

Variation - BY ALL Attribute

The BY ALL clause locks the value of a metric at the specific attribute aggregation level in only a single attribute hierarchy. Other attribute hierarchies are not affected.

What tab:

SELECT Amount BY ALL Year (Closed)

This metric always retrieves the same value for the sum of all deals on the Year aggregation level, regardless of stage or status. Any time attribute inserted in the report is ignored. In the following example, a time value is in the third column and does not affect the values in the other columns: 

If another attribute is added outside the Date (Closed) hierarchy, the lock respects the Year attribute only. For example, if a Product attribute is added in the How tab, the same deal amounts per product for all the years are displayed:

Variation - BY ALL IN ALL OTHER DIMENSIONS

BY ALL IN ALL OTHER DIMENSIONS is an application of BY ALL to all attribute hierarchies (dimensions). Within the metric, it includes in the calculations all of the attributes from all dimensions. If this clause is applied to the previous example, the last column of the report always contains the deal amounts, regardless of how the report is sliced. The following example is showing the difference:

The Amount [BY ALL Year] has the aggregation level lock set to Year (Closed). So, the deal amount is the same for all the years per product; these values are represented in the rectangles displayed above for each product. The Amount [All in All] metric has the lock on all attributes across the whole model, so the deal amounts are the same, as shown in the blue rectangle.

Variation - BY ALL IN ALL OTHER DIMENSIONS EXCEPT FOR Attribute

Adding the EXCEPT FOR attribute clause to the BY ALL IN ALL OTHER DIMENSIONS clause specifies an exception where the metric may be sliced and diced by the specified attribute and its hierarchy, when the attribute is included in the report.

What tab:

SELECT Amount BY ALL IN ALL OTHER DIMENSIONS EXCEPT FOR Owner

The metric generates one number across the entire data model, regardless of how the report is sliced and diced. When the Owner attribute is added in the How tab, the above report is modified:

The Amount [ALL in ALL] remains unchanged, but the Amount [BY ALL EF Owner] is respecting the additional attribute.

Variation - BY Attributes ALL IN ALL OTHER DIMENSIONS

This clause complements the BY statement by specifying how the aggregation of the metrics should be calculated in all other dimensions not specified in the BY statement.

Omitting ALL IN ALL OTHER DIMENSIONS allows the metric to be sliced and diced in other dimensions. It is useful when your need to create a metric that is fixed at highest attribute aggregation level in all dimensions, but it is still possible to slice and dice on the Date dimension.