exclude

info

This is part of our beta expression language AQL. Learn more. Request beta.

Definition

The macro exclude() is used when you want your measure 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

Syntax

tip

exclude_grains(), of_all() are aliases of exclude(). It may be more intuitive to use 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)

exclude(measure, model, ..., dimension, ...)

Examples

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

orders.total_orders | of_all(orders.status) // -> Total orders of all order statuses

Input

• measure: An AQL measure
• 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

Output

A modified measure 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
    }

    measures 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 | exclude(order_items))
    ;;
  }
}

With exclude(order_items), the total_amount measure 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: