Subtotals in Looker

lookml

(andy) #1

Update: There is now a custom table visualization that supports subtotals! For more info, visit the table with subtotals custom visualization page.


We often hear requests to produce subtotals in Looker explores. Previous methods used Derived Tables, but were hard to maintain if a change was required. Additionally, it was difficult to enable such explores to apply user filtration to subtotals, and to automatically use basic SQL when subtotals are not requested by users.

Here’s a pattern with which you can add flexible subtotaling to any existing explore. (NOTE: the SQL used below is in the Redshift dialect and must be adapted if you are using a different sql database)

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 all values from the product dimension rolled up to the value ‘Subtotal’.

To get the full story, both are included together and sorted to produce the final table:

The key here is that the underlying data shown twice, once in the regular rows and again in the subtotal rows. This can be achieved by cross joining the original data with a two row table. A cross join forces every row in the left table to join with every row in the right table. When the right table has two rows, the result is, effectively a duplicate view of the left table.

Consider how our granular data would appear after cross joining to a two row table:

If we then have the product column simply say ‘Subtotal’ on all subtotal rows, we get:

And when the above is aggregated and sorted appropriately, we get our desired subtotals:

Here’s an explore with a cross joined ‘subtotal_over’ view added, which works like the example described above:

explore: order_items {
  # Join other views as usual.
  join: products {
    sql_on: ${order_items.product_id} = ${products.id} ;;
    relationship: many_to_one
  } 
 #Join the subtotaling view using a cross join.
  join: subtotal_over {
    type: cross
    relationship: one_to_many
  }
#Optionally, additional levels of nested subtotals can be enabled as follows
  join: subtotal_over_level2 {
    from: subtotal_over
    type: cross
    relationship: one_to_many
    #when adding a level of nested subtotals, need to add this sql_where to exclude the generated row which would subtotal over the higher level, but not over this lower level.
    sql_where: not (${subtotal_over.row_type_description}='SUBTOTAL' and not ${subtotal_over_level2.row_type_description}='SUBTOTAL') ;;
  }

#While you can pivot subtotal_over dimensions as you'd expect, if you want to see subtotals for BOTH regular dimensions and pivot dimensions simultaneously, add another layer of subtotaling as follows
  join: subtotal_over_for_pivot {
    from: subtotal_over
    type: cross
    relationship: one_to_many
  }
}

In the subtotal_over view, we’ll add fields that we want to enable users to subtotal over. The sql of these fields will either resolve normally or will resolve to the text ‘SUBTOTAL’, depending on the Row Type.

Note that, for each field, we’ll need to create a hidden field which handles the sort order, and the order_by_fields’ SQL differs slightly by field type.

Additional dimensions to subtotal over can be added by simply copy-pasting a field of the right type, along with its order_by_field, and adjusting the dimension name and base field reference.

view: subtotal_over {
  sql_table_name: (select '' as row_type union select null as row_type) ;; #This sql table name is used to create a duplicate copy of the data. When rowType is null, fields from this view will resolve to 'SUBTOTAL'

  #used in sql parameters below to re-assign values to 'SUBTOTAL' on subtotal rows
  dimension: row_type_checker {
    hidden:yes 
    sql: ${TABLE}.row_type ;;
  }
  # used for readability in sql_where of nested subtotal join
  dimension: row_type_description {
    hidden:yes 
    sql:coalesce(${TABLE}.row_type,'SUBTOTAL');;
  }

#######################################
### Example String Based Dimensions ###
  dimension: product_name {
    order_by_field: product_order
    # For subtotal rows: show 'SUBTOTAL'.  For nulls, show '∅' (supports intuitive sorting).  Otherwise use raw base table field's data. Note, concatenation with '${row_type_checker}' is used to concisely force subtotal rows to evaluate to null, which is then converted to 'SUBTOTAL'
    sql: coalesce(cast(coalesce(cast(${products.name} as varchar),'∅')||${row_type_checker} as varchar),'SUBTOTAL');;
  }
  dimension: product_order {
    hidden: yes
    #For order by fields, use a similar calculation, but use values that correctly put nulls at min and subtotals at max of sort order positioning
    sql: coalesce(cast(coalesce(cast(${products.name} as varchar),'          ')||${row_type_checker} as varchar),'ZZZZZZZZZZ');;
  }

#######################################
### Example Number Based Dimensions ###
  dimension: sale_price {
    order_by_field: sale_price_order
    sql: coalesce(cast(coalesce(cast(${order_items.sale_price} as varchar),'∅')||${row_type_checker} as varchar),'SUBTOTAL');;
  }
  dimension: sale_price_order {
    hidden: yes
    type: number
    sql: coalesce(cast(coalesce(cast(${order_items.sale_price} as float),-9999999999)||${row_type_checker} as float),9999999999);;
  }

#####################################
### Example Tier Based Dimensions ###
  dimension: sale_price_tier {
    order_by_field: sale_price_tier_order
    sql: coalesce(cast(coalesce(cast(${order_items.sale_price_tier} as varchar),'∅')||${row_type_checker} as varchar),'SUBTOTAL');;
  }
  # Tier based dimensions work similarly to string fields, but need to leverage Looker's built in Tier Sorting dimension by adding '__sort_' to the base field name in the order by field
  dimension: sale_price_tier_order {
    hidden:yes
    sql: coalesce(cast(coalesce(cast(${order_items.sale_price_tier__sort_} as varchar),'          ')||${row_type_checker} as varchar),'ZZZZZZZZZZ');;
  }

#####################################
### Example Date Based Dimensions ###
  # Note that you can use one dimension group for order_by_field, but must create each timeframe separately.
  # Timeframes that represent contiguous datetime ranges, like day, week, month, quarter, year, etc, work as shown here.  Conversely, for timeframes like month_num or day_of_week, you can use the string pattern or numeric pattern from above as appropriate.
  dimension: created_year {
    order_by_field: created_order_year
    sql: coalesce(cast(coalesce(cast(${order_items.created_year} as varchar),'∅')||${row_type_checker} as varchar),'SUBTOTAL');;
  }
  dimension: created_quarter {
    order_by_field: created_order_quarter
    sql: coalesce(cast(coalesce(cast(${order_items.created_quarter} as varchar),'∅')||${row_type_checker} as varchar),'SUBTOTAL');;
  }
  dimension: created_date {
    order_by_field: created_order_date
    sql: coalesce(cast(coalesce(cast(${order_items.created_date} as varchar),'∅')||${row_type_checker} as varchar),'SUBTOTAL');;
  }
  dimension_group: created_order {
    hidden: yes
    type: time
    timeframes: [raw,minute,hour,date,week,month,quarter,year]
    #for date fields, use _raw version of the base field, and use datetime datatype and defaults in the order by field's sql.  1900-01-02 and 9999-12-30 used to remain as valid dates in case of any timezone conversion.
    sql:  coalesce(cast(coalesce(cast(${order_items.created_raw} as datetime),'1900-01-02')||${row_type_checker} as datetime),'9999-12-30');;
  }
}

As a finishing touch, consider applying formatting parameters as desired (value_format_name, group_label, description, etc). Note that formatting is not inherited from base fields. Also, adding an HTML parameter such as the following can help visually distinguish subtotal rows from regular rows

html:{% if value == 'SUBTOTAL' %}<b><i><span style="color: black;">SUBTOTAL</span></i></b>{% else %} {{ linked_value }}{% endif %};;


Group By Table Calculation in Visualization
Subtotals using Native Derived Tables and Templated Filters
A cure for the one_to_many blues
Subtotals with Table Calculations
Subtotal Help - State and City
Input For How To Visualize Number Of Active Goals Per User
(Max Glantzman) #2

Just realized my question isn’t relevant. :slight_smile:


#3

Hello - How would you make all the columns bold for the subtotal row instead of only the subtotal over view’s column? Thx.


(molly.lippsett) #4

Hi @Trevor_z

Could you clarify a bit what you would like to bold? Is it the word “Subtotal” in the column being subtotalled (as in the example above in the Product Name column)?
If so, we can probably do this with some html formatting in the LookML for that dimension, to bold the rendered value of the field if it’s = “SUBTOTAL”

Best,
Molly


#5

Hi Molly,

Thanks for the troubleshooting help. I’d like all columns in the subotal rows bold. I’ve attached a screenshot. The word subtotal is bold, but I’d also like the state and count to be bold.


(Izzy) #6

@Trevor_z tossing my ideas in the ring here too, @molly.lippsett feel free to pop in if you’ve got anything better!

Because liquid html lets you reference other fields values using the view_name.field_name._value syntax, it’s only a tad more complex to apply that same html formatting to all fields.

In the product_name dimension, we use this html:

html:{% if value == 'SUBTOTAL' %}<b><i><span style="color: black;">SUBTOTAL
</span></i></b>{% else %} {{ linked_value }}{% endif %};;

To apply the formatting to all the columns, where the above html checks {% if value == 'SUBTOTAL' %}, we instead want to check {% if products.product_name._value == 'SUBTOTAL' %} to reference that specific column.

So, if you changed the fields to look like

dimension: category: {
    type: string
    sql: whateverthesqlmaybe ;;
    html: {% if products.product_name._value == 'SUBTOTAL' %}
                        <b><i><span style="color: black;">{{linked_value}}</span></i></b>
               {% else %} 
                        {{ linked_value }}
               {% endif %};;
}

and

measure: count: {
    type: count
    html: {% if products.product_name._value == 'SUBTOTAL' %}
                        <b><i><span style="color: black;">{{linked_value}}</span></i></b>
               {% else %} 
                        {{ linked_value }}
               {% endif %};;
}

it should check to see if the name column says “SUBTOTAL” on that specific row, and if it does, bold and italicize the values for all fields.

lemme know if there’s anything fuzzy about that!


#7

Thanks Izzy, that worked like a charm. Trevor