Hey there, happy 2018.
I have a table that includes:
state, type and count
There are 50 states and each state has multiple types and each type has a count.
It looks generally like this
state type count
ca a 50
ca b 3
ca c 5
ca d 12
wa d 13
wa b 14
wa e 11
wa a 28
ny c 39
ny a 12
ny b 52
ny d 11
I’d like to have a list of states and just the type with the highest count per state. Then, I’d like to get a percentage that shows how many of the records in that state are of that particular type.
So it looks like this –
state type count percent
ca a 50 71%
wa a 28 42%
ny c 52 46%
How do I do this in looker? Thanks!
@sebastian, it looks like we’ll want to rank the count amount for each state. Depending on what your dialect supports we will need either a correlated subquery or a window function. This is possible with some creative SQL one-off coding… but what about re-use? What if you want the 2nd highest count per state, or least? Looker gives us the flexibility to do this!
Using your general table from above we define table
SCHEMAas a view file in Looker:
Using SQL Runner we can create another view
derived_windowfrom a SQL query that uses a window function to rank the types by highest count per state.
Which gives us results like this:
We can achieve identical results with a correlated subquery, in which case we would replace our derived table
sql:clause with the following:
Either way we will want to join the derived table view to the original
stcview in our model:
When we go to the explore, we can select our desired fields (State, Type, maxcount from the derived table, and Type_Sum from stc). Then we can filter on Rank = 1 or 2 or none at all if we want to see percentages for all of the counts. From there we have all the parts we need for the percentage calculation. Which can be done with a percent formatted table calc using
This is really great, thank you Jeffrey!
@sebastian - I also have an article about this subject here: