Skip to main content

Advanced Expressions

Overview​

Within the metrics view YAML, you can apply aggregate SQL expressions to create derived metrics or non-aggregate expressions to adjust dimension settings. SQL expressions are specific to the underlying OLAP engine, so keep that in mind when editing directly in the YAML.

Examples in Docs

For most of the examples here, DuckDB is being used. However, most if not all of the functionality is possible on different OLAP engines. You will need to refer to that specific OLAP's reference documentation. Don't hesitate to reach out to us if you have any questions!

tip

Rill's modeling layer provides open-ended SQL compatibility for complex SQL queries. More details can be found in our modeling section.

Measure Expressions​

Measure expressions can take any SQL numeric function, a set of aggregates, and apply filters to create derived metrics. A reminder on basic expressions is available in the create metrics view definition.

See our dedicated examples and pages for the following advanced measures!

Dimension Expressions​

To utilize an expression, replace the column property with expression and apply a non-aggregate SQL expression. Common use cases include editing fields such as string_split(email, '@')[2] to extract the domain from an email or combining values concat(domain, child_url) to get the full URL.

 - name: domain
   display_name: Domain Name
   expression: string_split(email, '@')[2]
   description: "Extract the domain from an email"

See our dedicated examples and pages for the following advanced dimensions!

Druid Lookups​

For those looking to add ID to name mappings with Druid (as an OLAP engine), you can utilize expressions in your Dimension settings. Simply use the lookup function and provide the name of the lookup and ID, i.e. lookup(city_id, 'cities'). Be sure to include the lookup table name in single quotes.

 - label: "Cities"
   expression: lookup(city_id, 'cities')
   description: "Cities"