Subtotal Help - State and City


#1

Hello -

I’m playing with implementing this pattern,

https://discourse.looker.com/t/subtotals-in-looker/6422

and am hoping I could get some pointers on cleaning it up a bit.

There is a table of registered users containing the city and state they registered from. I want to show the registered users by city, grouped by state, with subtotals.

user_registered looks like so:

view: user_registered {
sql_table_name: ruby.user_registered ;;

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

dimension: city {
type: string
sql: ${TABLE}.city ;;
}

dimension: state {
type: string
sql: ${TABLE}.state ;;
}

dimension_group: timestamp {
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.timestamp ;;
}

dimension: user_id {
type: string
# hidden: yes
sql: ${TABLE}.user_id ;;
}

measure: count {
type: count
drill_fields: [detail*]
}

#----- Sets of fields for drilling ------
set: detail {
fields: [
user_id
]
}
}

subtotal_over looks like so:

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’);;
}

dimension: city_name {
order_by_field: city_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(${user_registered.city} as varchar),‘∅’)||${row_type_checker} as varchar),‘SUBTOTAL’);;
html:{% if value == ‘SUBTOTAL’ %}SUBTOTAL{% else %} {{ linked_value }}{% endif %};;
}

dimension: city_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(${user_registered.city} as varchar),’ ')||${row_type_checker} as varchar),‘ZZZZZZZZZZ’);;
}
}

and the explore looks like so:

explore: Location {
view_name: user_registered
join: subtotal_over {
type: cross
relationship: one_to_many
}
}

The generated SQL looks like so:

SELECT
user_registered.state AS “user_registered.state”,
coalesce(cast(coalesce(cast(user_registered.city as varchar),’ ')||subtotal_over.row_type as varchar),‘ZZZZZZZZZZ’) AS “subtotal_over.city_order”,
coalesce(cast(coalesce(cast(user_registered.city as varchar),‘∅’)||subtotal_over.row_type as varchar),‘SUBTOTAL’) AS “subtotal_over.city_name”,
COUNT(DISTINCT user_registered.id ) AS “user_registered.count”
FROM ruby.user_registered AS user_registered
CROSS JOIN (select ‘’ as row_type union select null as row_type) AS subtotal_over

GROUP BY 1,2,3
ORDER BY 1
LIMIT 500

Let me attach a picture of the explore and hopefully I’ll be able to get a pointer on why the grouping and sorting might be off.


(Davis Clark) #2

Hi Trevor - I don’t work for Looker, and I haven’t spent much time digging into your post…

That said, at first glance, it looks as though you’ve only sorted on State, while your City Name dimension is what holds your custom sort logic.

Try sorting on State, and then, while holding shift, sort on City Name.

Also, you should check out the Subtotals custom viz. I’ve never been given so much user love for so little effort in my life.


#3

Well that worked super swell. I did set up the subtotal viz in Looker and have been using it, but thought it would make me a better person if I tried implementing the subtotals directly as well.