How to build a Drill Down Hierarchy Chart | Organisational Chart in Tableau| Tableau Decision Tree
Вставка
- Опубліковано 4 гру 2020
- How to build Tableau drill down tree| Tableau Organisation Chart| Tableau hierarchy tree | Tableau Data Lineage| Decision Tree. Tableau training for beginners | Tableau tutorial for beginners
Download Tableau Public : public.tableau.com/profile/gu...
P.S - Last few minutes of this tutorial has no audio as it got corrupted. Although it should not impact the use of the tutorial as you can still see all the steps.
Apologies for the inconvenience.
--------------------------------------------
📚 Recommended Books
--------------------------------------------
📚 DATA VISUALISATION & STORYTELLING
Storytelling with Data: A Data Visualisation Guide for Business Professionals amzn.to/3q9ygPw
Data Visualisation: A Handbook for Data Driven Design amzn.to/3qfb1na
The Big Book of Dashboards amzn.to/3q4i3eG
Data Visualization: A Practical Introduction amzn.to/44KYrel
Storytelling with Data : Lets Practice amzn.to/44O7WcL
Tableau Desktop Specialist Certification: amzn.to/44Ocy2P
Tableau Desktop Certified Associate: amzn.to/3Kk2YfV
📚 SQL
SQL for Data Analytics: amzn.to/3rROkGj
SQL 3 books in 1 : amzn.to/3KklUuY
Learning SQL: Generate, Manipulate and Retrieve Data : amzn.to/3Kn34mN
SQL Quick Start Guide: amzn.to/3KInOpx
📚 DATA ENGINEERING
Data Engineering with Python : amzn.to/3YelH1Y
Data Engineering with AWS: amzn.to/3YgXoAL
Fundamentals of Data Engineering: amzn.to/3OAUVxy
Software Architecture: amzn.to/3KmNffX
System Design Interview: amzn.to/3Yhfy5q
Ace Data Science Interview: amzn.to/3rOxcBl
System Design Interview: amzn.to/3OAw8d7
Designing Machine Learning Systems: amzn.to/3YbGxiB
📚 SOFTWARE ENGINEERING
Clean Code amzn.to/47aBnr4
Clean Architecture amzn.to/3OA8VaP
The Programmatic Programmer: amzn.to/4793k2C
-----------------------------------------
👇 CHECK THIS OUT! 👇
-----------------------------------------
🏪 AMAZON STOREFRONT
www.amazon.com/shop/datavizca...
All my recommendations for books, gadgets and gear are also available at my
Amazon storefront. Check it out!
👕 LIKE THE "DATA RELATED GIFTS" WITH CREATIVE, FUNNY & HUMOROUS QUOTES FOR ANYONE WHO LIKE TO LAUGH ?
You can purchase one here: www.datavizcanvas.com/shop/#!/
--------------------------------------------------------------------------------
😍 📧 REACH OUT TO ME ON SOCIAL MEDIA 😍
--------------------------------------------------------------------------------
Instagram: / datavizcanvas
Twitter: / datavizcanvas
Facebook profile.php?...
Linkedin: / gurpreetsingh515
Tableau public public.tableau.com/app/profil...
Website : www.datavizcanvas.com/
-------------------
GEAR I USE
-------------------
📷 Video
Canon EOS RP amzn.to/3QkOpfU
Canon RF 35mm amzn.to/3rNIh5E
Canon RF 14-35mm amzn.to/44JQw0Y
🎙️ Audio
Rode NT-USB Versatile Studio amzn.to/3DCbxyY
Rode PSA1 Professional Studio Arm amzn.to/3ObseWN
⏱ TIMESTAMPS
‼️ DISCLAIMERS
Links included in this description may be affiliate links. When you buy a product or service with these links, I may receive a small commission. However, there is no additional cost to you :) I genuinely appreciate you supporting my channel so I can continue to provide you with awesome content for free!
Thanks for being a part of this channel and all your support! 💪 🙏
Amazing example. I tried and it worked and you explained really well in an understandable way. Thanks Much - Jan
Thanks Janani for your feedback
Thanks for sharing!
Looking forward to seeing more such content.
Thanks Abhinav. Stay tuned 😊
Great work man!
Much appreciated 😊
Happy to help!
Thanks for the video, it really help, please can you explain how you got the last two fields "Path" & "END NODE ID" ... I am doing a project on this but it is kind of complex
This is amazing! I've been trying to figure this out for weeks. With this tutorial, you have taught me 90% of what I need to create a dashboard to show an application and it's dependencies from a data dump out of our company's CMDB. Thank you so much for sharing! I'm having a little trouble labeling Node 0, and a little trouble understanding the sequence of numbering in the 'Path' column when it gets past 2 levels. Do you have any hints?
Thanks @John. I am glad it was helpful.
In regards to labelling the nodes, I have used a calculation @Label something like below. This will help to find the exact node that you want to be labelled and add the text value in the calculation and bring it to the text mark.
if ([@X Axis])= -6 and [END_NODE_ID] = 0 then [FINAL_OBJECT]
ELSEIF [START_NODE_LEVEL] = 1 and ([@X Axis])= 6 then [DEPENDENCY_NAME]
//ELSEIF [START_NODE_LEVEL] = 2 and ([@X2])= 6 then [OBJECT_NAME]
ELSEIF [START_NODE_LEVEL] = 2 and ([@X Axis])= 18 then [DEPENDENCY_NAME]
----- and so on
end
Sequence numbers in the path field is basically referring to the hierarchy or one branch in the org chart 0|-2|-3|-4|-4|-4
Above path means that the branch started from start node id : 0 and ended at end node id :-2.
End node ID : -2 becomes the start node id for next layer of the branch and so on.
Hope that helps.
Please feel free to reach out if you need any assistance.
Cheers!
Thanks for this video! May I know where can I get the formulae related to the sigmoid curve? Where can I find the X-axis formula you used in the calculation field?
I set up my data the same way you did, however when I drill down to below the 4th level and below, the nodes overlap and start showing up under the wrong parent nodes.
Example: If Parent node 1.1.1 has child node 1.1.1.2 with end_node_id 1 and Parent node 1.1.2 has child node 1.1.2.1 with end_node_id 1, then both child nodes overlap. The labels over lap and everything.
Also sometimes if a child node has a end_node_id that's adjacent to those of a drilled down parent node, it will show up under that incorrect parent node.
Thanks for your feedback.
In regards to the overlapping nodes or labelling the nodes. There are 2 options
Option 1:
I have used a calculation @Label something like below. This will help to find the exact node that you want to be labelled and add the text value in the calculation and bring it to the text mark.
if ([@X Axis])= -6 and [END_NODE_ID] = 0 then [FINAL_OBJECT]
ELSEIF [START_NODE_LEVEL] = 1 and ([@X Axis])= 6 then [DEPENDENCY_NAME]
//ELSEIF [START_NODE_LEVEL] = 2 and ([@X2])= 6 then [OBJECT_NAME]
ELSEIF [START_NODE_LEVEL] = 2 and ([@X Axis])= 18 then [DEPENDENCY_NAME]
----- and so on
end
Sequence numbers in the path field is basically referring to the hierarchy or one branch in the org chart 0|-2|-3|-4|-4|-4
Above path means that the branch started from start node id : 0 and ended at end node id :-2.
End node ID : -2 becomes the start node id for next layer of the branch and so on.
Option 2:
In this example the end nodes were created automatically using python code so had the overlapping end node id. If you want it to be separate then please use the distinct end node id for each level and node
Hope that helps.
Please feel free to reach out if you need any assistance.
Cheers!
This is fantastic and so useful. Thanks for taking at least a second to explain how/why you're doing something rather than just do this, do that. Is there a way to "roll" the hierarchy back up? Can one add a calculated field to the "Clearing selection will: Set value to:" field?
Thanks for your feedback Chris.
In this example you can select the individual node to drill down as well as if you select the node at one level above it will roll back the hierarchy. This is based on the parameter action which assign the path value to the parameter when we select the node and it compares it to Start Node ID. If it matches it shows the drill down list and the same logic applies for roll back of drill down.
I haven't tried the "Clearing selection with Set value to " field but thats an another option that can be looked at. Thanks for sharing new ideas :-)
Please do try that and share with the community. I am always excited for new ideas :-)
Thanks once again
@@DataVizCanvas Thanks!!
Thank you for the awesome tutorial,
Would it be possible to roll up the nodes once drill down is done. Can you please help to show a tutorial on it.
Thanks for your feedback Asutosh. You could roll up just by clicking on a previous node as it is driven by the parameter action. Hope that answers your question. Please try an let me know if this is what you are looking for. If not then please share a specific scenario and i will try my best to assist you :-)
Cheers!
This is such a great tutorial. Thank you so much for the step by step and calculation explanations. I do have a question - I have this working as detailed in the video. Is there a way to keep the strings visible as you drill down? I think this would be done in the Show Path calculation, but I can't figure out what to do to keep the branches as you drill down. I would greatly appreciate any help. Thank you!
Were you able to figure this out ? @amy Mcdonagh
Great video! Thank you for sharing. I am having trouble with the node0 node0 node1.3 0 1 2 FALSE 0 0 0|0 node 1.3 gets mixed up with node 0. None 0 picks up info from node1.3. I follow the exact same logic as it your video.
Hi Alexandra. Thanks for your feedback. Could you please provide more detail on the issue you are facing. Alternative you could download the dashboard from my public profile and you could follow that along with video. Hope that helps.
Download Tableau Public : public.tableau.com/app/profile/gurpreet.singh2669/viz/HowtobuildDrillDownHeirarchyOrganisationChart/OrganisationChart
Please feel free to reach out in case of any issues.
Cheers!
Gurpreet
Hi! This was so helpful. I have one question though - the drill down functionality works great in the worksheet view, but it does not function the same way in dashboard view, which I need to publish. How can I fix this? Please let me know if I can provide additional information. Thank you!
Thanks for your feedback :-) . Please try to apply action filter again in dashboard that you applied in worksheet. By default when you move worksheet to dashboard ,all the "actions" that you applied to worksheet are removed and you have to reapply it again in dashboard. Hope that helps. Please try and let me know if it still doesn't work. Cheers!
Hello! This video was super helpful and I was able to build a chart like this! I am stuck on a few points though;
I am having a hard time with formatting the nodes to apply labels, for example I am labelling them to represent different programs and the details of those programs. I ended up changing the object name on the data set to the program names and this worked but didn't give me any control over the formatting. However when I tried to add text in the label option in tableau it applies to all the nodes instead of just the one I want to relabel, is there a way to fill out each one individually? I am also wondering about the @drill down label on each node, can I make this not viewable? I don't want it to say "drill down" I just want it to drill down when clicked.
And last thing I was wondering about, I was wanting the chart to zoom in when a node is clicked, so if I click node 1.1 I want it to become more of the focus of the screen and same as you go down the tree still with the option to click on the one above to zoom back up, is this possible? Sorry for all the questions and thank you for any help you can provide!
Hi, how were you able to format the data for this? Did you also use this for an organizational chart?
This is awsome, but i want to change the points of @colour, by photos because i reconverse in a tree family and want to put the photo. i must change some calculated filed? thanks
You could try to use the "Colour Calc" and use that in shape mark to add the photos you want.
This is very helpful, thank you ! How do I add values on the link from one node to the other please?
I am not clear with your question. Could you please provide more details.
Hi Mounika, Just following up if your issue is resolved. Please feel free to reach out in case of any issues.
Cheers!
This was really helpful, thank you! I was wondering if there was a way to make sure the child nodes properly align up with the correct parent nodes (no overlapping) without having to make sure each node has a unique end node id? I have to add over 1000 different nodes so this would make it much easier! I've already implemented the calculation @Label that you show us in the video but it still overlaps.
Hi Faatemah,
The only way to do that is by having unique end node id.
If you have only 1-2 levels then you can also try multilevel Dendrogram chart. Check out below playlist for details:
ua-cam.com/play/PLMbGQrGl-0EuBew3uS85ZitX_Evc8oJp2.html
Hope that helps
Gurpreet
@@DataVizCanvas Thank you I will check out the Dendrogram Chart! For this hierarchy chart, which functions would we need to change to increase the @X Axis values so that there is a greater distance between the points? Right now I believe they're set to -6, 0, 6, 18, ... but I am trying to increase them in Tableau.
@@faatemahmallick6041 We have used the Range value from -6 to 6 with an increment of 0.25
We have used that in the @X axis calculation. You can change the range value or the calculation itself to increase the distance. As i have used 50 points in range, you could try to increase it from 50 to 100 and it will help you to increase the distance. Hope that helps.
This is really helpful! Thanks for sharing. One question, however, for creating the path. Is there a programmatical way to derive the "path" automatically rather than being hardcoded? Just imagine we are creating an org chart that can show thousands of employees. Any insight would be helpful! Thank you!
Thanks for your feedback. That’s a great question. We had a similar requirement and we used python code to programmatically drive the path based on the start node and end node values. Hope that helps 😊
@@DataVizCanvas Could you please either make a video showing how you did this or provide some dummy code that would show the general steps you took?
Hello, This was a super Video. But I had a doubt, can we have any logic to automate the path columns with there relations like based on Drilldown.
Hi Sarath,
Thats a good question.
You could use python, altryx or any other data manipulation tool to automate this. You can use node level (Start and end node) in the logic base don your requirement.
Hope that helps.
Cheers!
I’m trying to prep the data for large data with more than 10,000 rows, can you please help me with some direction. How can I assign the node levels and node paths dynamically. I’m using tableau prep for the preparing data.I’m working on this from past 2 weeks but I have not made any progress. Please help me. Thank you in advance.
did you managed to solve it?
This is an awesome technique but i feel it opened a can of worms , we want to add so many different ways of drilling down the Hierarchy. Is it possible to drilldown all the drillable nodes 3-4 levels down. Ex: clicking Node 1.1 should drilldown 1st level 2.1 & 2.2(if drillable) 2nd level down 3.1 to 3.4, 3rd level down 4.1, 4.2 so on. users don't want to drilldown each node, they wanted 3-4 levels automatically drilled. Appreciate sharing the video.
This is definitely possible by just updating the Show path calculation. In this example I have used parameter action to update the parameter value that I have used in Show Path calculation. You could update this calculation to achieve the desired result. Please try that and let me know in case of any issues. Hope that helps.
@@DataVizCanvas Hello, great video! I have the same problem as the guy above, and I am wondering if you can shed more light on this! Thank you!
greetings, i have found the video very helpful and after several trials and errors i finally gave up and need some help.
It started as a simple quest to make a family tree but at the end of the data compilation there is a node ( specifically node 0 and node1.4 which overlap , im guessing since node 1.4 has coordinates 0|0 but i cant seem to separate node0 from it since i havent given it coordinates. everytime it try they just overlap.) my issue is that a child cell (1.4) shows up as my final object ( node 0 ).
Its my first time working with tableau and im a little lost.
Wishing you all the best kind sir.
Hi Amarildo,
Thanks for your feedback.
If i understand it correctly, you are having issues with overlapping nodes.
If thats the case then you need to try to change the coordinate i.e.two nodes should not have the same end node. or in other words, the coordinates needs to be unique. In this tutorial, I have specifically kept few overlapping coordinates as I wanted to show how we can use parameter action to overcome that issue. If you see this video till end you will understand.
If you need more help then please share the link to your work on tableau public and i will have a look and advise.
Also there is another way to achieve same results. Please check another tutorial for that: ua-cam.com/video/FWrHY0kpplY/v-deo.html
Hope that helps.
@@DataVizCanvas
Thank you for the quick reply kind sir.
Unfortunately this isn't the issue as i have Specifically created a data set that DOESNT have overlapping parameters. I need to have a visual of all nodes to create a map ( Family tree ).
I followed the video ( all 3 actually ) and i was able to get the end result by following step by step instructions. But unlike this video, where you explain what the data in the excel file represents, on that one, I couldn't edit THIS data to match the requirement as I couldn't find anywhere How it should be compiled or what's the relation between cells.
Im a complete beginner, so i have no previous exp in this sort of stuff, so forgive me if i missed something along the way.
The public link for reference:
public.tableau.com/app/profile/amarildo1459/viz/MyFamilyTree/Sheet1#1
The issue here is that on some nodes, i can see CHILD node names, rather than PARENT node names, and in some others its fine ( While having the same calculation ).
When you zoom in, you can see both node names, its just one overlaps the other i guess? Is there a way around it? as you can see for example, node1.0 , and some other nodes dont show at all
Is there a way to make the lines longer( Along the x axis )? Im guessing maybe by changing the (range -6 to 6 then scaling everything else accordingly? )
Greatest regards from Albania. Subbed and liked.
@@amarildoprifti143 You are doing a great work as a beginner and are on the right track with your Family tree.
You have just missed one step to label the nodes based on calculation i.e. @Label
If you see the tutorial, that step is at 17:21 where i have created the @Label calculation. This calc will overcome the overlap label issue.
Also in the text mark make sure you have this calculation as a text label under point shape mark.
I have also fixed your workbook and published in my profile for your reference and can be accessed using below link:
public.tableau.com/views/FamilyTreemockup/FamilyTree?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link
Also you can adjust the axis range to make the line longer.
Is that ok if i keep it published on my profile so others in the tableau community can refer to this piece of art you have created?
Please feel free to reach if you need more info.
Hope that helps.
Cheers!
Gurpreet
@@DataVizCanvasoooh i see, i wrote 6 instead of 0!!! its cases like these that i think it would be even more helpful if you can write the formulas on the description so people can copy paste rather than pause and type like i did. leads to lots of typos. but i guess i learned more out of it than just copy pasting.
of course :) Although there's a lot more to fix on it, formatting and all.
for example as you might have noticed i added a row for names, so i can later replace nodes with a real name, now when i try to drag name into label it becomes a mess again. is that not how you do it?
re: the question. i just replaced dependency name data with the name data in the excel sheet and it worked. guess its all fitted for calculation around that row.
Any good ideas on how to make this better?
Oh right, if i was to print this on a pdf file, what combination would you rec? page size, scaling and all that. the bigger the better.
Very nice video. But i have problem, when on level 2 i don't have a Dependency object directly below the Final object meaning when the path does not lead straight back to zero from the root. For example, I have two dependencies -2 (0 | -2) and 2 (0 | 2) so I don't see the root. But when I give 0 (0 | -2) and 2 (0 | 2) I get the root. But he's not pretty balanced.
Could you please share the link of your dashboard on tableau public so that I fully understand your problem.
Can you create an custom sql or make the process much easier to make it dynamic as per the department level
That’s a good question @shaunak. It depends on the raw data. All you need is the data structured the way shown in the video. You can use different tools to achieve same results. It’s quite easy to do with python and altryx if you are familiar with them.
@@DataVizCanvas thank you can you share the spreadsheet. Can we use data densification method
@@Shaun1810 You can download the data and the tableau workbook from my tableau public profile
public.tableau.com/views/HowtobuildDrillDownHeirarchyOrganisationChart/OrganisationChart?:language=en&:display_count=y&:origin=viz_share_link
I am creating this chart for a large organization, I have spent over 2 weeks and about to give up.mu data is a bit more complex since some child nodes are not unique. Do you tutor privately? Please help me to figure this out
Hi Niloufar, could you please share more details about the dataset issue and also the tableau public link to your dashboard that you have built so far. I can have a quick look.
Why are we using 1 as join calculation to join data and range sheets? Can we use any thing other than 1? Please explain on what basis we are using 1 to join the two sheets.
Hi Sushree,
I have used "1" as a join calculation to return the cartesian product of rows. Its also know as cross join which combines each row from first table with each row from second table. You can use any number instead of 1 as well till the time you use same number in both the side of join condition. Hope that helps 🙂
Hello!
I see a problem aat node 5.2; Though the Node 5.2 has a value of Drill Down=False, clicking on this node shows Node 6.3 and Node 6.4 from Level 6. On examining the path variable for Node 6.3 and Node 6.4, I realized that there's no relation between Node 5.2 and these nodes. Nodes 6.3 and 6.4 are actually child nodes of Node 5.3. This is happening because Nodes 5.2 and 5.3 share the same End_Node_ID in the spreadsheet.
Is there any way to get rid of this problem?
Hi Anirudh, Thanks for the feedback.
You are right. This is happening because node 5.2 and 5.3 are sharing the same End_Node_ID. That was the main reason I used the drill down feature to show how we could avoid the overlap in the visualisation. Another way to overcome this scenario is to make sure that the END_Node_ID are unique for all combination. Once you have all those END_NODE_ID unique then you don't even need to use drill down feature.
Hope that helps.
Please feel free to reach out if you need more infromation.
@@DataVizCanvas Hi Gurpreet, Thanks for making this video. Really helpful presentation. As Anirudh raised, there is no relation between 5.2 and nodes 6.3 and 6.4. If a node has multiple parents, it will be useful to show relationship if you drill down on either one of parent. The example you have shown hides a relationship between one parent and child. Cant a child node have multiple paths?
If we are having end_node_id unique for all combination, we need to show the node twice if an end_node_id has multiple parent which will not fit well in real world examples.
@@bala5562 Thats a very good point and you are correct. If node has multiple parents then you can show it without using the drill down feature. In this particular example I just showed the ways to achieve the drill down feature. This technique can be modified to achieve desired results based on an individuals requirement.
IS THIS THE SAME WITH DECISION TREE ?
Same logic can be used to build a decision tree as well
I have a dataset containing raw information about employee, their position in the organization and whom they report to. How do I convert this data into the above dataset that you started out with? It would be good if you could also share the python script for converting the raw data into the above dataset that you started out with
Unfortunately I don't have the python script for this scenario. I will try to make a seperate tutorial on that. Cheers!
@@DataVizCanvas Thanks so much for the great tutorial! I am also very interested in how you automatically derived the Start and End Node IDs, and the Path. Were you able to create a tutorial on how to do this? Thank you again for your help and insight!
How should i create this structure off data? any help? any comment please
Hi Subhajit, There was a similar scenario at my work project and python was used to structure the data. You could use Altryx as well. Hope that helps
@@DataVizCanvas Can i use Tableau prep instead of altryx to prep the data?
@@sairamreddysin Yes you can use tableau prep as well .
@@DataVizCanvas Thank you for your reply. I am stuck at creating X, and Y positions(nodes) step. how can I create those automatically for 12,000 employees? we are not using python in our project.
How to access the raw data ?
Hi, You can download the dashboard from my tableau public profile and can export the data as well. Hope that helps.