Can you create an organizational hierarchy in Tableau that you can drill down and drill up? You can, but you have to set up your data in a certain way. I will use a Harry Potter example from my Tableau Public profile here.
I found a video in Youtube by Data Viz Canvas on how to do this here.
I watched the video and added most of the presenter's calculations in the blog below. I simplified the steps and modified some of the calculations for our use case. I also simplified the data structure below and will walk you step by step through the set up.
When I think of Harry Potter, I think of Hogwarts. That will be my highest level name. Then I think of the four houses, Gryffindor, Ravenclaw, Hufflepuff and Slytherin.
I will need to create a spreadsheet with the following columns;
Highest Level Name - This will be "Hogwarts" displaying the highest level
Level 1 Name - The purpose of this column is for placement. I will explain the details a little later
Level 2 Name - This will contain the house names, and characters
Dependency Type - This will always be 0
Start Level # - This explains what level you want the item to start at
End Level # - This is where the level will end
Drill-down Flag - This column will be a TRUE or FALSE value
Start ID - This numeric column will display where the item starts
End ID - This numeric column will display where the item ends on the y or horizontal axis.
Path - This is the start ID plus a pipe and the end ID.
The image below is how the data is set up for the first level.
I gave the houses a Level 1 Name of "Level0" so you know they will always be displayed and this is the start of our hierarchy. I gave them a Start Level # of 1 since this will be the start of our drill downs. They technically end at Level 2 which is our End Level #.
I also gave them a Drill-Down Flag of "TRUE." I want the user to click on the house name and be able to see the families that are in that house. This will enable this in our dashboard. For any that I don't want the user to drill-down it will be a "FALSE."
The Start ID is 0 since this is the start of our hierarchy.
The End ID is -2 for Gryffindor since I want this house to appear first on the Y axis. Hufflepuff is -1 since I want that house to go second. Ravenclaw is 0 and will appear directly underneath Hogwarts. Slytherin is 1 so it will appear last and on the right.
The Path column is a combination of the Start ID and the End ID with a "|" in between.
Here is a view of the data for the next level down;
Next I want all the parents of the Harry Potter etc in the next level. I gave them a Level1 for their Level 1 Name but added a different number for each group. 1.1 is for the house Gryffindor. 1.2 is Ravenclaw. 1.3 is for the parents in Hufflepuff. Finally all of the people in 1.4 are Slytherin. There are only one group of parents here, to show you what it would look like for singles at this level.
You can see the Start Level # is now a 2, since we are starting a second drop-down and it ends on a third level.
Lily and James Potter as well as Molly and Arthur Weasley have a Start ID of -2, because they are starting at Gryffindor. They have different End ID's so that they are not overlapping each other. Molly and Arthur will be first since they are at -3. Then Lily and James will be next to them at -2. The path now has a second pipe and a number after it. It retained the path from Gryffindor of "0|-2" but then the End ID for these parents was added after.
Here is a view of the data for the child level;
This is now the fourth level down which is our End Level 4. We also have different End ID's to have all of the Weasley children side by side. They retained the Gryffindor Path and have their Parent's path of -3. They now have their own End ID. This is added after another "|."
Hopefully this data structure makes sense. You can continue to add lower levels but keep in mind they will have to retain the path of the parent and have unique End ID's. It's all about the layout on that Y Axis. I had to show my headers for both axis to understand the layout.
Cross Database Join
I created a separate sheet for densification called Range with a column called range. The purpose of data densification is to create additional marks in the view to “compensate” for missing values. In this column I had the values -6 and ending in 6 in .25 intervals.
I joined this spreadsheet to my other spreadsheet based on a inner join calculation of 1 using a cross database join.
You have to double-click on the Harry Potter Hierarchy file to get a blue tab on the left hand side. Then you can drag the range data set to the canvas and it will prompt you for your join fields. (You have to then click on Edit Join Calculation and add a value of one on the Join Calculation pop-up menu.)
Next go to your sheet and you will need to create the below parameter.
Parameter
You will need to create the below Calculated Fields;
X Axis
[Range] + (([Start Level #]-1)*12)
Point
if first()=0 or last()=0 then sum([X Axis]) END
Sigmoid Curve
1/(1+EXP(1)^-[Range])
Curve
[Start ID]+(([End ID]-[Start ID])*[Sigmoid Curve])
Path Calc
IF [Point] = -6 then '-' else ATTR([Path]) END
Show Path
IF [Start Level #] = 1 then TRUE
ELSEIF [Start Level #]=2
AND STR([Start ID])=split([Path Parameter],'|',2)
THEN TRUE
ELSEIF [Start Level #]=3
AND STR([Start ID])=split([Path Parameter],'|',3)
THEN TRUE
ELSEIF [Start Level #]=4
AND STR([Start ID])=split([Path Parameter],'|',4)
THEN TRUE
ELSEIF [Start Level #]=5
AND STR([Start ID])=split([Path Parameter],'|',5)
THEN TRUE
ELSEIF [Start Level #]=6
AND STR([Start ID])=split([Path Parameter],'|',6)
THEN TRUE
ELSE
FALSE
END
Label
If ([X Axis]) = -6 and [End ID]= 0 then [Highest Level Name]
ELSEIF [Start Level #]=1 and ([X Axis])=6 then [Level 2 Names]
ELSEIF [Start Level #]=2 and ([X Axis])=18 then [Level 2 Names]
ELSEIF [Start Level #]=3 and ([X Axis])=30 then [Level 2 Names]
//we stopped at 3 but this is in case you needed more
ELSEIF [Start Level #]=4 and ([X Axis])=42 then [Level 2 Names]
ELSEIF [Start Level #]=5 and ([X Axis])=54 then [Level 2 Names]
ELSEIF [Start Level #]=6 and ([X Axis])=66 then [Level 2 Names]
ELSEIF [Start Level #]=7 and ([X Axis])=78 then [Level 2 Names]
ELSEIF [Start Level #]=8 and ([X Axis])=90 then [Level 2 Names]
ELSEIF [Start Level #]=9 and ([X Axis])=102 then [Level 2 Names]
ELSEIF [Start Level #]=10 and ([X Axis])=114 then [Level 2 Names]
end
Drill Down Label
IF [Label] = "Hogwarts" then 'Reset Selections'
ELSEIF [Drill-Down Flag] = TRUE then 'Drill-Down' ELSE '' END
Color Calc
If Isnull([Label]) = true then "Null"
else "Orange"
END
Null Highlight
''
Steps to Create the Chart
Place Curve on the Columns shelf.
Place the X axis on the Rows shelf and place Point next to it. Make these a dual axis and synchronize the axis.
Click on the All Marks card.
Place Highest Level Name and Level 2 Names on the Detail Marks card.
Place Range on this Detail Marks card but make it a Dimension.
Change the X Axis Marks card to a Line mark type.
Then click on the Point Marks card.
Place the Color Calc on the Color Marks card
Place the Path Calc to the Detail Marks card.
Place Show Path on the Filters Marks card. Check the box for True when prompted.
Right-click on the X axis and check the box for Reversed.
Add Null highlight to Detail.
Change the shape to whatever you wish for the drill down icon. I find a button icon online.
Increase the size of the shape.
Then right-click on the Null in your Color Calc legend and hide it.
Perform housekeeping items such as getting rid of all the grid lines, row dividers, column dividers etc.
Add the Drill Down Label to the Label of the Point Marks card.
Then place Label on your Label Marks card. Adjust your labels so that the Drill-down text is inside of the shape and that the Label is below the shape.
Place this sheet on a dashboard.
You will need to set up two dashboard actions.
The first is a parameter action;
The second is a Highlight action to disable the highlight on the buttons.
With these two actions in place, you should now be able to drill down on the buttons for the different levels. I stopped at a fourth level but you can add more.
You can add other things such as images in the tool tip. You can also add the True / False trick to remove highlighting.
Comments