Purpose

In LookML you can use the tier parameter to group a numeric dimension's data into ranges you define. There might also be scenarios where you want to group information that is only available as a measure, not as a dimension. For example:

• You want to define cohorts by a count value, such as customers by lifetime orders or salespeople by number of sales per quarter.
• You want to compare a set of dates, such as a specific order date and the same customer’s first order date (when first_order_date doesn’t exist as its own dimension).

To do this, you'll need to treat your measure as a dimension and use a derived table to define your tiers. This article will walk you through the steps.

Example

Let’s dive a little deeper into the first example we gave above, cohorting based on a count measure. Here is a sample portion of an orders table in your e-commerce database:

A sales manager asks, “Do my salespeople who sell the fewest orders actually bring in the most revenue?” Your task now is to cohort your salespeople into tiers based on how many orders they have sold. The final metric is going to look like this:

What You Need

You will notice that the original orders table doesn't have a column telling us how many sales each salesperson has made. We would have to create a measure for that -- but we wouldn't be able to calculate that measure based on a cohort, since SQL doesn’t allow grouping by the results of an aggregate function. Put another way, we can only group by dimensions. What we need is a way to change the count of lifetime sales into a dimension, and then create a tier dimension from it.

We call this “dimensionalizing a measure.” This is because we are taking a measure, in this case a count of sales, and using it as a dimension. In Looker, we accomplish this by creating a derived table that expresses the measure we want.

Two Solutions

You can solve this with either a native derived table or a SQL-based derived table.

Native Derived Table

Native derived tables (NDTs) offer good governance because they interact with your existing model, and are very useful after a brief learning curve. You can learn more about NDTs in Looker on the Creating Native Derived Tables documentation page.

To use a native derived table to tier your measure:

1. Start by setting up your results in the Explore interface. In this case, we’d choose the Salesperson ID dimension and the Count of Sales measure. The result might look like this:
   
2. Click the Explore's gear menu and select Get Derived Table LookML. Looker displays the LookML to create the corresponding NDT.
3. Copy the LookML. For a screenshot of this step, see the Creating Native Derived Tables documentation page.
4. Paste the LookML into a view file in your project. Don’t forget to remove any row limit clause that may appear in the derived table LookML. For instructions on accessing project files and creating a view file, see the Understanding Model and View Files documentation page.
5. Add a tier dimension to group the salespeople's lifetime sales into cohorts. Our example uses tiers of 0-10, 11-20, and 21 or more.
6. Join the table to your original Explore via the field used in the group_by clause. In this case, that would be the salesperson ID.

SQL-Based Derived Table

To use a SQL-based derived table to tier your measure:

1. Start by setting up your results in the Explore interface. In this case, we’d choose the Salesperson ID dimension and the Count of Sales measure. The result might look like this:
   
2. In the Data section, click SQL.
3. Choose Open in SQL Runner.
4. Once in the SQL Runner, you can choose Add to Project from the gear menu. Don’t forget to remove any row limit clause that may appear in the derived table SQL.
5. Add a tier dimension to group the salespeople’s lifetime sales into cohorts. Our example uses tiers of 0-10, 11-20, and 21 or more.
6. Join the table into your original Explore via the field used in the group by clause. In this case, that would be the salesperson ID.

Results

Whether you've used a native derived table or a SQL-based derived table, each salesperson’s lifetime order data is now available as a cohort dimension in the original Explore. The resulting table in our example looks like this:

Our Explore now includes this extra dimensionalized measure, so we can produce the original desired table by using the Lifetime Sales Cohort from the derived table as our dimension, and a sum of the order values as a measure.