In most SQL dialects, one can only do math between dimensions (things in the group by) and measures (aggregates, not in the group by) when said dimensions are in the query.
Omni restricts these query-structure contingent fields in the data model by default, but they can be built using in_query to model around corner cases.
Here’s a quick example that is not allowed
sale_price_per_year_of_life:
sql: ${order_items.sale_price_sum}/${users.age}
Note how we are mixing measures (order_items.sale_price_sum) and dimensions (users.age). This will only work is users.age is in the query and thus is blocked by default. To support we simply lean on in_query:
sale_price_per_year_of_life:
sql: CASE WHEN {{ users.age.in_query }} THEN
${order_items.sale_price_sum}/${users.max_age} ELSE 'Add users.age to compute'
END
Note that we have replaced the dimension with an aggregate (users.max_age), but the aggregation will only be computed with users.age is in the query - otherwise we’ll provide a warning to the user.
Here is the result:
