of_all

Definition

The function of_all() is used when you want your metric to not be evaluated against certain dimensions/grains, even when that dimension/grain is present during exploration. All filters applied on the excluded dimensions are also ignored.

This is useful when you mix an aggregation of a lower grain with a higher grain, like when calculating Percent of Total

tip

exclude_grains(), exclude() are aliases of of_all(). We recommend using of_all() when writing metrics to express your intent more clearly. E.g. orders.total_orders | of_all(orders) reads more naturally than orders.total_orders | exclude(orders).

Syntax

of_all(metric, model, ..., dimension, ...)

Examples

orders.total_orders | of_all(orders) // -> Total orders of all orders
orders.total_orders | exclude(orders) // -> Total orders of all orders

orders.total_orders | of_all(orders.status) // -> Total orders of all order statuses
orders.total_orders | of_all(orders, keep_filters: true) // -> Total orders of all orders, keeping outer filters

Input

• metric: An AQL metric
• model (optional, repeatable): The model that you want to exclude from the calculation, along with all dimensions within this model and related dimensions through an n-1 relationship
• dimension (optional, repeatable): The dimension that you want to exclude from the calculation
• keep_filters: (optional): A boolean value that specifies whether to keep the filters applied on the excluded dimensions. Default is false.

tip

keep_filters is useful when you want the metric to be calculated on the filtered data of the excluded dimensions. For example, to calculate the percentage of each status of orders after certain date:

• You cannot use of_all(orders.status) because it would return the percentage over orders across all time.
• Instead, use of_all(orders.status, keep_filters: true) to calculate the percentage over the filtered orders.

Output

A metric with the specified dimensions excluded from their Level of Detail (LoD) context.

Sample Usages

Suppose you have an Ecommerce dataset that contains information about order items, and you want to calculate the percentage of sales contribution by each country:

Model order_items {
  dimension quantity {}
  dimension price {}
  dimension country {}

  dimension order_value {
    definition: @aql order_items.price * order_items.quantity
  }

  measure total_amount {
    definition: @aql sum(order_items.order_value) ;;
  }
}

Your Percent of Total metric would be calculated by dividing total_orders of each country by the total_orders of all countries. The definition of that metric will be as follows:

Dataset ecommerce {
  models: [order_items]

  metric percent_of_total {
    definition: @aql
      order_items.total_amount / (order_items.total_amount | of_all(order_items))
    ;;
  }
}

With of_all(order_items), the total_amount metric in the divisor will not be grouped or filtered by any dimensions in the order_items model, as well as any other models that have n-1 relationship with it.

Result:

The Percent of Total is still correctly calculated when you include dimensions from users model:

Note that, since we're using of_all() without keep_filters: true, the percentage is calculated over orders of all time. This approach is useful, for instance, when determining the percentage of sales attributed to the top five countries. From the results, we can observe that the top five countries contribute 54% of the total sales, and we can also see the contribution of each individual country to overall sales.

In the case we want to compare the top five countries against each other, we can use of_all() with keep_filters: true to calculate the percentage based on the filtered data.

  metric percent_of_total {
    definition: @aql
      order_items.total_amount / (order_items.total_amount | of_all(order_items, keep_filters: true))
    ;;
  }

With that, we can see that, while United States only contributes to 11% of all sales, they contribute to 21% of the sales of the top five countries.