Tiers on a measure

  • 18 November 2015
  • 3 replies

I know looker has a Dimension type of tier but can I create a tier for a measure.

Use Case: I need to count the transactions for a buyer and determine which tier that buyer is in.

Name     | Trucks Purchased

Company1 | 30-40

Company2 | 20-30

Or create a histogram of the numbe of companies in each range (‘Trucks Purchased’)

3 replies

Userlevel 6

@srivera There is a really nice Looker block that describes how to do this:

[Analytic Block] User Loyalty and Other User Attributes

Hi @lloydtabb - Are you basically saying that he will need to create a derived table first to aggregate each company’s “Trucks Purchased”, then bring them in as dimensions so that he can tier them?

If that’s the case, how would you be able to make this dynamic? e.g. Be able to view the # of Trucks Purchased at varying timeframes.

Userlevel 6

Assuming the derived table from this model on

- view: user_order_facts


## highlight

sql: |


orders.user_id as user_id

, COUNT(*) as lifetime_items

, COUNT(DISTINCT order_items.order_id) as lifetime_orders

, MIN(NULLIF(orders.created_at,0)) as first_order

, MAX(NULLIF(orders.created_at,0)) as latest_order

, COUNT(DISTINCT DATE_TRUNC('month', NULLIF(orders.created_at,0)))

as number_of_distinct_months_with_orders

, SUM(order_items.sale_price) as lifetime_revenue

FROM order_items


GROUP BY user_id

sortkeys: [user_id]

sql_trigger_value: SELECT MAX(id) FROM orders

## endhighlight


- dimension: user_id

primary_key: true

hidden: true

- dimension: lifetime_items

type: number

sql: COALESCE(${TABLE}.lifetime_items,0)

- dimension: lifetime_orders

type: number

sql: COALESCE(${TABLE}.lifetime_orders,0)

- dimension: lifetime_orders_tiered

type: tier

style: integer

tiers: [0,1,2,3,5,10]

sql: ${lifetime_orders}

We can use the lifetime number of orders tier, and count the number of users in each tier.

Suppose we want a 30 day count (the number of orders in the last 30 days)? We would add another computed value in the derived table query.



CASE WHEN orders.created_at + interval 30 day > CURRENT_TIMESTAMP

THEN order_items.order_id


END) as orders_30_days


then the dimensions

  - dimension: orders_30_days

type: int

- dimension: orders_30_days_tiered

type: tier

style: integer

tiers: [0,1,2,3,5,10]

sql: ${orders_30_days}

You could also do this dynamically using parameters, but it makes for a confusing enduser experience. Adding an attribute on user, number of 30 day orders, its probably the best bet.