First of all, this is not a valid operation in the SQL/LookML world. Just as it is NOT possible to perform
SELECT
(SUM(order_items.amount) - product.cost) AS order_profit
FROM order_items
LEFT JOIN order_items.product_id = product.id
But the end result is possible. Let’s walk through a few examples:
###1) Measure::Measure operation
Let’s assume we have the following views and dimensions/measures defined:
- view: orders
fields:
- dimension: id
primary_key: true
- dimension: order_price
type: number
- view: product
fields:
- dimension: product_id
primary_key: true
- dimension: product_cost
- view: order_items
fields:
- dimension: order_items_id
primary_key: true
- dimension: order_id
- measure: total_product_cost
type: sum
sql: ${product.product_cost}
- dimension: product_id
- explore: order_items
joins:
- join: orders
foreign_key: order_items.order_id
- join: product
foreign_key: order_items.product_id
And our objective is to calculate the profit on each order. Normally, we would have total_item_price measure in the order_items view from which we would subtract the total_product_cost measure.
However, frequently we have other order-specific fees associated with every order that force us to make use of the order_price dimension value that is captured within the orders table - not within an order items table.
Since we cannot subtract a measure (order_items.total_product_cost) from a dimension (orders.order_price), we create two new measures:
- view: orders
fields:
- measure: total_order_price
type: sum
sql: ${order_price}
- measure: total_order_profit
type: number
sql: ${total_order_price} - ${order_items.total_product_cost}
In this case, both total_order_price and order_items.total_product_cost are measures that are calculated over unique orders and corresponding unique items. Even though any query that pulls total_order_profit will fan-out both orders and product tables, we have ensured that product_cost is correctly duplicated across all items and is aggregated at the item level, while orders are aggregated only at the order level (e.g. total_order_price = order_price for every unique order_id record).
###2) Dimension::Dimension operation
Another solution is to convert a measure to a dimension by creating a fact table (aka derived table).
- view: order_item_facts
derived_table:
sql: |
SELECT
order_items.order_id
, SUM(product_cost) AS order_cost
FROM order_items
LEFT JOIN product ON order_items.product_id = product.product_id
GROUP BY 1
fields:
- dimension: order_id
primary_key: true
- measure: order_cost
type: number
And the associated change in the explore:
- explore: order_items
joins:
- join: orders
foreign_key: order_items.order_id
- join: product
foreign_key: order_items.product_id
- join: order_item_facts
view_label: 'Orders' #syntax available starting 3.20
foreign_key: order_items.order_id
Now the operation we are looking for happens at order level as expected:
- view: orders
fields:
- dimension: order_profit
type: number
sql: ${order_price} - ${order_item_facts.order_cost}
