_edited_edited.png)
While working on project, I discovered a new advance chart i.e. “Dendrogram Chart.” Dendrogram is an advance tableau chart. In this blog I am going to explain what dendrogram is, how to create dendrogram with the help of tableau and what are advantages and disadvantages of Dendrogram.
Dendrogram shows hierarchical clustering which looks like tree so that Dendrogram is also called Tree chart. The dendrogram is Greek word, dendron means “Tree” and gramma means “Drawing”. The dendrogram consists of “Clade” and “Leaves”. Clade means “Branches”. Clades have one or infinity leaves.
Here we can how to create dendrogram chart using “Sample Superstore dataset”.
First connect the data source which is “Sample Superstore” to the tableau.
After connecting sample superstore data, now create “Path field” on excel Sheet.
We want to create “Inner Join” with Orders and Path filed. For that double click on the Orders and then the physical layer will open on tableau.
Now copy the Path filed from excel sheet and paste in the physical layer.
“Sheet 1” is Path field. Now we will create a joint.
First it shows error. Then click on “Add new join clause.” Then click on “Create join calculation.”
For creating join, type 1 in the create join calculation in “Orders” and type 1 in “sheet 1” which is Path field.
Now “Inner Join” is formed between Orders sample superstore and Path field.
After creating join, we can see that there are two path values (0 and 270) for every row in the dataset.
Creating Path is also called “Data Densification”.
Data densification” means increasing density or increasing more numbers of points in the dataset.
Go to the worksheet and create Path bin.
Click on “Path” then create and then “Bins”.
Select the size of path bin 1.
Create calculation field for the Sale using Window sum function.
Calculation Field:
Sales_D= Window_Sum (Sum (Sales))/2
In these calculations, we are dividing 2 by window_sum because when we create join with path filed (0,200) that time duplicate values for sales are created.
Next calculation is Total_Sum_D, which is exactle same as Sale_D calculations.
Calculation Field:
Total_Sum_D=Window_Sum (Sum (Sales))/2
Next Calculation filed is Percentage.
Calculation Field:
Percentage_D= Sales_D/Total_Sum_D
Next Calculation is Percentage_Adjusted_D.
Calculation Field:
[Percentage_D]/WINDOW_MAX([Percentage_D])
Next calculation Filed is Rank_D. In this calculation Rank_Unique function is used.
Calculation Field:
Rank_D=RANK_UNIQUE([Sales_D], 'desc')
Next calculation filed is creating X-axis. i.e. X_D
Calculation Filed:
X_D= ((INDEX()-1)*0.12)-6
In this calculation Index function is used. This calculation gives equal accurate spacing for data densification which we created as “Path Field”.i.e..(0,200)
Next calculation is Sigmoid.
Calculation Field:
Sigmoid=1/(1+EXP(-[X_D])
Now let’s calculate the Y-Axis.
Calculation Filed: Y_D=[Sigmoid]*([Rank_D] -(WINDOW_MAX([Rank_D]) +1)/2)/100
Next calculation is “Size”.
Calculation Filed:
Size_D= IF [X_D] >=6 AND [X_D] <= 6+(10*[Percentage_Adjusted_D])
THEN 1ELSE 0 END
These are 9 calculations for standard dendrogram.
Now let’s start to create a Dendrogram.
First change the Mark type to “Line”.
Take Sub-Categories to Color Mark.
Drag and drop Path (bin) into Column.
Right Click on Path (bin) and Check on Show Missing Value.
Drag and drop Path (Bin)in to details under Marks.
Put X_D into Column and Y_D into rows
Now Click on X_D and select compute using and then Path (Bin)
Again, click on Y_D then select compute using and then select Path (Bin)
You see now the following window.
Now this curve is created.
Then Go to the Y_D and Edit the table calculations.
In the Nested calculation select Y_D
In compute using select “Specific Dimension”
Select “Sub-Category” only.
In Rank_D select “Specific Dimension”.
And then Select “Sub-Categories” only.
Now you can see “Dendrogram Chart” is appeared.
There are three options to change the thickness of bars in “Dendrogram.”
The first option is:
1. Drag and drop Sales_D (Calculation Field) into “Size” under marks and adjust the thickness of Bars.
The second option is:
2. Drag and drop actual "Sales" in the “Size” and which we can see circles appearing on Dendrogram. We can adjust the size of circles.
The third option is:
3. Drag and drop “Size_D” (calculation Field) into the size and adjust the thickness of bars on dendrogram.
For creating bars on dendrogram click on the “Size_D” and then select “compute using” and then select “Path (Bin)”.
Now we can see the Bars on dendrogram.
These all bars having same length. So, if we want change size of bars according to sales then followings are steps:
Click on Edit table calculations.
In the Nested calculation Select” Total_sum_D”
In compute using select “Specific dimension”.
Select Path (Bin) and Sub-Categories and place Sub-category on the top.
Again, in the Nested Calculation select “Percentage_Adjusted_D”.
In the computer using select “Specific Dimension”.
Select Path (Bin) and Sub-Categories and place Sub-category on the top.
Now we can see changes the bar size according to the Sales number.
Now if we want to see bars in reverse order then,
Click on axis and then select “Edit axis.”
Then “Edit axis Y_D” window will appear and, in that select, “Reverse”.
Put Sales into the “Labels “under the marks and click on “Quick table calculations” and
Select “Percent of total”.
Now let’s do Formatting.
Hide X_D axis and Y_D axis.
Hide the Grid lines.
Hide the zero lines.
Let’s go to another sheet and create the total numbers of sales.
Drag and drop Sales into the “Labels” under Marks.
Edit the labels, increase the font size of Sales.
Now we see the total sales number.
Right click on Sales number, then select formatting. In Formatting first select “total numbers of sale” and then select Numbers, and then select “custom currency”.
Now let’s create a Dashboard.
Go the dashboard and drag and drop worksheets on dashboard.
There are two options Tiled and Floating.
Select floating.
Then hide the title of the worksheet.
Click on “Fit” and select any option like “Fit Width”,” Fit height” and “Entire View” and adjust the worksheet.
Add “Title “on Dashboard i.e. Dendrogram.
In Title Change the font size, make it Bold. This is our final “Dendrogram Chart.”
Advantages of Dendrogram:
1.Most of the times Dendrogram consists of complex data structures and hierarchical clustering relationships between objects. But because of Dendrogram it is easy to read and identify grouping and clustering patterns at once glance.
2. If you are not a technical person then also you can quickly understand dendrogram and easily identify grouping and clustering patterns.
3.It is tree like structure with clades and leaves that will help us which patterns are similar and forms clustering.
Disadvantages of Dendrogram:
1. Creating dendrogram with large datasets takes a lot of time.
2. While creating dendrogram if any data field is changed then we must create new dendrogram.
Summary of Dendrogram:
Dendrogram is a very helpful tool for analyzing data and grouping the data. Dendrogram is tree like structure with clades and leaves, because of that it is easy to understand clustering patterns. But dendrogram is ineffective if the data set is large and complex.
Comentários