1 Reply Latest reply on May 2, 2016 2:54 PM by diego.medrano

    Creating a complex hierarchy - need to expand/collapse on a variable at each level, but not drill down on it; also: "keep onlys" and drilling back up

    Suzanne Crockett

      SUMMARY: I’m creating a set of text tables that I want to be able to drill down three layers into (on the columns). A basic hierarchy would sort of suffice but the problem is that I want to be able to collapse or expand the data by year at each level. If my example hierarchy is State -> City -> District and I put the year variable after the hierarchy in the ‘columns’ bar, each level is broken out by year by default and there is no way to collapse the year. Example:

      (in the real dataset, this would make the table very, very wide -- the first level has about ten columns, each broken into three years)


      If I put the year variable within the hierarchy and I try to order the variables as State->Year1->City->Year2->District->Year3, as soon as someone filters by year after state, the city and district variables will be filtered there, as well. And because I would need three year variables, I can’t just have a single user-toggled filter to show or collapse year.


      This gets complicated further by the fact that the three levels (variables) have a lot of values in them – about 150 unique combinations, or 150 columns when the hierarchy is fully expanded, so users will have to do a lot of “keep onlys” to drill down into the layers (drilling down by clicking a column header drills down on ALL columns, not just the one selected), meaning that if I keep only one city and drill into the districts for that city, then drill back up, I'll only see the city I kept.  Users will need to use the filters and reselect all values in the filters when they want to drill back up and go to a different city or state.


      I’m looking to set up these tables to be used by end users with a wide range of tech savviness, so I really want to make this as intuitive as possible.


      I’ve tried splitting each hierarchy into three dashboards and using action filters to move across each one as I drill down, but there doesn’t seem to be an easy way to drill back up. If I create some sort of “back button” to move from the district level back to the city level, moving back to the city level will show cities in all states – not just the state I filtered on. 


      What the envisioned end process is looks like this:

      I first want to be able to compare all the states to each other on a given metric. Then I want to choose a state and compare its score for that metric across year, or maybe just go straight into the cities for that state. I want the ability to compare the scores in a given city by year or break out that city by its district. When I reach the end, I want to be able to go back up and see all the cities within my given state, then go back up and see all the states again.


      I created an example workbook with a dashboard that shows the issue with the simple hierarchy, and a set of three dashboards that are linked together with action filters, so I feel like I’m part of the way there.


      Any help or direction to go is appreciated.