bottom

DefinitionCopy Anchor Link

Find the bottom N values of a dimension, based on specified measures. The bottom rows are determined by the specified measures, in ascending order. Default to use 'skip' logic, which is the same as the rank function. To use 'dense' logic, use the logic: 'dense' parameter.

Syntax

bottom(n, dimension, by: measure_expr, ...)
bottom(n, dimension, by: measure_expr, ..., logic: logic)

bottom(10, users.name, by: count(orders.id)) // -> Table(users.name)
bottom(10, users.name, by: count(orders.id), by: average(users.age)) // -> Table(users.name)
bottom(10, users.name, by: count(orders.id), logic: 'dense') // -> Table(users.name)

Input

• n (required): The number of bottom values to return.
• dimension (required): A fully-qualified reference to a dimension. The output table will have one row for each bottom value of the specified dimension.
• by (repeatable, required): A measure that is used for ranking. The bottom rows are determined by the specified measure, in ascending order. E.g. bottom(10, users.name, by: count(orders.id))
• logic (optional): The logic to use for ranking. Default to 'skip'. To use 'dense' logic, use logic: 'dense'. E.g. bottom(10, users.name, by: count(orders.id), logic: 'dense')

Output

A new table with one row for each bottom value of the specified dimension.

Sample UsagesCopy Anchor Link

bottom(5, users.full_name, by: count(orders.id))

Notice that the their are 6 users in the bottom 5 because there are users with the same number of orders.

Using dense rank logic would return even more users because their rank is consecutive. E.g. The users below the 2 bottom-1 users would be ranked 2 instead of skipping to 3.

bottom(5, users.full_name, by: count(orders.id), logic: 'dense')

Bottom is especially useful when used with where to filter measure to only include the bottom values.

explore {
  measures {
    _total_orders_of_top_5:   
      count(orders.id) | where(users.id in bottom(5, users.id, by: count(orders.id)))
  }
}

You can also use it for nested aggregation, such as finding the bottom 5 users by number of orders, then finding the average age of those users. (Note: orders.value is a measure)

bottom(5, users.id, by: count(orders.id)) | select(orders.value) | avg()

See alsoCopy Anchor Link

• top()
• group()
• rank()
• Pipe operator