Question

Creating hierarchical grouping between dimensions

  • 26 September 2017
  • 8 replies
  • 1932 views

Hi there,



I’m new to Looker and this question could be a silly one. Appreciate all the inputs I receive.



I have 3 dimensions State, City and Product and a measure number of Sales. In my table visualization, I want to see City, as child of State, and pivoted to give me the number of Sales across different products. Below visualization could give a better idea of wht I have in mind.





This sort of grouping could be done in Tableau so wondering how to do this in Looker.


Any ways I could achieve this? Thanks.


8 replies

Userlevel 2

Hi @archana,



This sort of grouping depends on how your City and State data is stored. Looker will not automatically know that “Buffalo” is a city in “New York” unless the relationship is pre-defined in your data.



For example, let’s say I have a simple table of City, State, and Number of users, where a row in the table looks something like:



'NYC' as city, 'New York' as state, 55 as users


'Buffalo' as city, 'New York' as state, 5 as users



When I view this data in an explore, I can pivot on both city and state, and add in a count of users to get the following:





I would suggest pivoting on city and state to try and recreate the grouping you are looking for. Please let me know if you have any further questions.

Hi Emma,



Thank you for replying. Yes, I’m able to create this earlier as well. Let me rephrase my question…could be more aligned to visualization. Is there a way I could have one column for New York State and under that 2 columns for each city - NYC and Buffalo?



Thanks

Userlevel 2

Hi @archana,



It’s not currently possible to have one column with two columns nested underneath it. I can pass this along to our Product team and let them know it’s something you’d be interested in seeing.



You can sort by state, so the cities will display together, like this:





But it will still display a column for state each city column.

Hi @emma.ware ,

 

can we know the current update on this, is it possible to apply hierarchies i.e., one column with two columns nested underneath it.

 

 

Regards,

Sivaprasad 

hi,

Are there any updates on this matter, without hierarchies we have to rely on naming conventions to tell the users these fields are from the same Hierarchy

You will get the same display as in Tableau by using subtotal visualization

 

Userlevel 6
Badge

https://docs.looker.com/exploring-data/visualizing-query-results/table-next-options#subtotals

Hi madhavi1   and @IanT 

yes indeed the table subtotal does show it the way it is needed but this Hierarchy interpretation should be build with the LookML Dimension and should be interpreted by all visuals 

i.e:

Dimension  Country  

     sql: <country>

    child : City
     sql: <country>:<city>

 

This way all visuals should collapse to country and expand when clicked on (the was the table does it now) but for bar charts and other visuals 
Also more importantly this provide a very elegant way to do MDM when this dimension spans different subject areas

Reply