Community

Subtotals using Native Derived Tables and Templated Filters

We often hear requests to produce subtotals in Looker explores. Here’s a pattern to do that using Native Derived Tables and a templated filter. The previous method (recommended before Native Derived Tables was a Looker feature) used only SQL Derived Tables. The advantages of this pattern is more rapid iteration towards a final product and easier maintenance. The only disadvantage is the need to learn new features and some minor initial setup.

First, let’s align on the goal. Consider this example:

Patty’s shop sells 12 products in 3 categories. The total number of items ordered of each product can be calculated using a simple count from the order_items explore. This might look something like this:

Patty’s accountant has asked to also see subtotals for each category. The subtotal rows use the exact same measure as the line item rows, except they have the product dimension obscured.

To get the full story, both are unioned together and sorted by category, giving the final table.

Since the measure is identical in both tables they share most of the same code. They are different only in that one has a real value for product, and the other coalesces all products into one common value (in this case the string “Subtotal”). We can replicate this behavior using a templated filter. The pattern requires both a filter-only field, and the application of that field in the field we want to modify with it. Here is what we’d use for this example:

filter: line_items_or_subtotals {
  type: string
  suggestions: ["Line Items","Subtotals"]
}

dimension: product {
	type: string
	sql: 
       case when {% condition line_items_or_subtotals %} 'Line Items' {% endcondition %}
         then ltrim(${TABLE}.product)
            when {% condition line_items_or_subtotals %} 'Subtotals' {% endcondition %}
         then 'SUBTOTAL'
       else null end;;
}

When used in the normal order_items explore, this would allow the end user to set a value to inject into the products field. If the user sets the line_items_or_subtotals filter only field to “Line Items,” this will cause the product field to actually show a product. But if the end user sets this to “Subtotals” the product field will show the word ‘Subtotal’ no matter what the product is on that data table row.

Native Derived Tables leverage code written in another place without needing to repeat that code. This means we can use them to create derived tables which make use of our line_items_or_subtotals field. Patty will need to build two native derived tables, one for the line item rows where the filter-only field will be set to “Line Item,” and another for the subtotal rows where it will be set to “Subtotal.”

This is the Native Derived Table to produce the line item rows.

view: pattys_shop_line_items {
  derived_table: {
    explore_source: order_items {
      column: category {field: order_items.category}
      column: product {field: order_items.product}
      column: count_order_items {field: order_items.count_order_items}
      filters: {
      field: order_items.line_items_or_subtotals
      value: "Line Items"
      }
    }
  }
  dimension: category {}
  dimension: product {}
  dimension: count_order_items {}
}

This is the Native Derived Table to produce the subtotal rows. This code is exactly the same as line item version except the filter value.

view: pattys_shop_subtotals {
  derived_table: {
    explore_source: order_items {
      column: category {field: order_items.category}
      column: product {field: order_items.product}
      column: count_order_items {field: order_items.count_order_items}
      filters: {
      field: order_items.line_items_or_subtotals
      value: "Subtotals"
      }
    }
  }
  dimension: category {}
  dimension: product {}
  dimension: count_order_items {}
}

Finally, the two NDTs are unioned together. This utilizes the cascading derived tables feature.

view: pattys_report {
  derived_table: {
    sql: select * from ${pattys_shop_line_items.SQL_TABLE_NAME}
     UNION
     select * from ${pattys_shop_subtotals.SQL_TABLE_NAME}
    ;;

  }
  dimension: category {}
  dimension: product {
      order_by_field: product_order
      html: {% if value == 'SUBTOTAL' %}
      <b><p style="color: black; background-color: silver; font-size:100%; text-align:right">{{ value }}</p></b>
      {% else %}
      {{value}}
      {% endif %} ;;
  }
  dimension: product_order {
    hidden: yes
    sql: case when ${product} = 'SUBTOTAL' then 2
        else 1 end ;;
  }
    dimension: count_order_items {type:number}
  }
  dimension: count_order_items {type:number}
}

Finally, an explore is created based on the final, unioned derived table.

explore: pattys_report {}

This explore will yield the desired end result.

For another method which is a bit more flexible, try this newer discourse article, Subtotals in Looker.

4 Likes

Similar design with UNION ALL of source table and derived aggregates at http://sqlfiddle.com/#!6/1fd0f/1

Very handy and nice styling.
One question: Will it not give me duplicated count of Order Items, if explore is used without the Product dimension, or how is this handled?

Thanks

I think you could make that dynamic. Using the “in query” feature you could toggle whether to use the DT with the union in the explore or something else instead. The model would detect whether you added the “use subtotals” field and if so it could actually change which table to join into the explore.

I just added a link to another article describing a way to make the subtotals form more dynamically. Check the end of the article for this link!

1 Like