Table with Subtotals - Sort by Measure

[Stanislav_Livsh]

I have a table with Subtotals and use standard table visuliation with collapsilbe items.

The table contains State - City dimensions (grouping hierarchy) and Sales measure.

Currently the table is always sorted by State.

As I see in the doc: https://docs.looker.com/exploring-data/visualizing-query-results/table-next-options

“The leftmost subtotal is always sorted. When you sort by multiple columns, subtotal columns are given precedence”

I need to get States sorted by Sales meaure in descending order, i.e. States with higher sales on top. And still I need the posibility to expand the cities also sorted by Sales (desc) within state.

How can I do this?

[victor_rt88]

Hey, I need to do this too, did you learn how to do that?

[finn_google]

Hi @Stanislav_Livsh / @victor_rt88  , I’ve included below a couple of suggestions. In the below example I’ll be using user count per country per traffic source (vs sales per state per city in the use case above)

1) Drilldowns: A quick and flexible way of seeing the breakdown would be configuring a drilldown, in this example adding “traffic source” as a drill_field for the country dimension: 

 dimension: country {
    type: string
    map_layer_name: countries
    sql: ${TABLE}.country ;;
    drill_fields: [age_bracket, traffic_source]
  }

On the front-end, we can then quickly drill into see the breakdown by traffic source:

The upside of this approach is that it involves minimal changes to the LookML and you can easily add several options or a hierarchy to split by (e.g. state, zip code, etc). The downside is that it doesn’t stick to the collapsible table viz you mentioned in the question, so another option that sticks to that viz is below.

2) Derived Table: As you found in the Looker docs, for subtotal sorting “The leftmost subtotal is always sorted and when you sort by multiple columns, subtotal columns are given precedence.” So to be able to sort first by the measure “user count” (or “sales” in your example), we can add the measure as a dimension to a derived table, in the below example finding the count of users per country for each row of the users table:

view: users_with_country_count {

  derived_table: {
    sql:
      SELECT
        users.id,
        COUNT(*) OVER (PARTITION BY users.country) AS count_of_users_per_country
      FROM `bigquery-public-data.thelook_ecommerce.users` users;;
      }

  dimension: id {
    primary_key: yes
    type: string
    hidden: yes
    sql: ${TABLE}.id ;;
  }
  dimension: count_of_users_per_country {
    type: number
    sql: ${TABLE}.count_of_users_per_country ;;
  }
}

And join it to our Explore:

explore: users {
  label: "Customer Behaviour"

  join:  users_with_country_count {
    relationship: one_to_one
    sql_on: ${users.id} = ${users_with_country_count.id} ;;

  }

Before finally on the front-end, we can select and hide the new "count_of_users_per_country" dimension (as the dimension which defines the subtotal ordering) as well as selecting all of the fields from the original query you mentioned. We get the final result by selecting the subtotals checkbox and applying the sort order within the subtotals by holding down the Shift key and selecting the “User Count” measure.