Tableau: #WOW2024 | Week 24 | Can You Visualise Headcount Distribution in Multiple Ways on a Single Sheet?
Integrating Diverse Chart Types on a Single Sheet
This week’s Workout Wednesday challenge (Challenge by Donna Coles) centers around presenting three distinct charts — stacked bar, side-by-side bar, and butterfly — on a single sheet. Users can dynamically select different dimensions for display and splitting based on various fields.
Enhance the data:
We need to create several calculated fields to enhance the dataset according to the specified requirements.
- Contract Type: Contract Type is either Full Time or Part Time. An employee is a full-time employee if their FTE (full time equivalent) value is 1
Create a calculated field for Contract Type:
Contract Type
IIF([FTE]= 1, 'Full Time', 'Part Time')2. Today: Create a parameter p.Today with current value 01 Dec 2022.
3. Age Calculation:
To calculate an employee’s age in whole years based on their birth date and the current date, create the following calculation.
Age
IF DATEADD( 'year' ,DATEDIFF('year',[Birth Date],[p.Today]) ,[Birth Date])> [p.Today]
THEN
DATEDIFF('year',[Birth Date],[p.Today]) -1
ELSE
DATEDIFF('year',[Birth Date],[p.Today])
END4. Age Bracket Calculation:
To categorize employees into age brackets such as Under 20, 20–29, 30–39, 40–49, 50–60, and Over 60 based on their calculated age, create the following field:
Age Bracket
IF [Age] < 20 THEN 'Under 20'
ELSEIF [Age]< 30 THEN '20–29'
ELSEIF [Age]< 40 THEN '30–39'
ELSEIF [Age]< 50 THEN '40–49'
ELSEIF [Age]<= 60 THEN '50–60'
ELSE 'Over 60'
ENDUser Options:
1. Users should be able to select from three different chart types, including Butterfly Chart, Stacked Bar Chart, or Side by Side Bar Chart.
- Create an Integer parameter: Display As — This parameter allows users to choose the preferred chart type for display purposes.
2. Breakdown Options include Age Bracket, Contract Type, Department, Gender, and Nationality.
- Create an Integer Parameter: Show Breakdown By.
3. Users can select to split each row by Gender, Contract Type, or choose neither.
- Create an Integer Parameter: Show Split By.
We need to display both the actual headcount and the percentage of the total for each row.
a) Create the following Calculations for the viz:
1. Selected Breakdown- determines the selected attribute for breakdown based on user choice:
Selected Breakdown
CASE [Show Breakdown By]
WHEN 1 THEN [Age Bracket]
WHEN 2 THEN [Contract Type]
WHEN 3 THEN [Department]
WHEN 4 THEN [Gender]
WHEN 5 THEN [Nationality]
END2. Selected Split — Color: Define the attribute for color splitting based on user selection — Gender or contract type or neither.
Selected Split — Color
CASE [Show Split By]
WHEN 1 THEN ''
WHEN 2 THEN [Gender]
WHEN 3 THEN [Contract Type]
END3. Headcount
COUNTD(Employee)4. Headcount for Charts: Adjust headcount for butterfly chart placement. When creating a butterfly chart, bars to the right of the central axis are shown with positive values, while those to the left display negative values (thus, the midpoint represents zero). Therefore, if selecting a butterfly chart for display, additional calculation is necessary to ensure that certain values, particularly Females / Part Time, appear correctly on the left side of the chart.
Headcount for Charts
IF [Diaplay As]= 3 THEN
IF MIN([Selected Split - Color]) IN ('Female','Part Time') THEN
[Headcount] * (-1)
ELSE
[Headcount]
END
ELSE
[Headcount]
END5. Total Employee — By Breakdown: Calculate total employees by the selected breakdown (it will be used in % of Total employee calculation per row.)
Total Employee — By Breakdown
{FIXED [Selected Breakdown]: COUNTD([Employee])}6. % Total Employee: Calculate the percentage of total employees for each row of selected breakdown.
% Total Employee
[Headcount for Charts]/SUM([Total Employee - By Breakdown])Format it to a percentage with 0 decimal point.
7. Display- Side by Side
CASE [Diaplay As]
WHEN 2 THEN [Selected Split - Color]
ELSE ''
ENDb) Creating the Sheet:
- Add Selected Breakdown and Display- Side by Side on the Rows shelf. Then, add Headcount for Charts and % Total Employee to the Columns shelf. Finally, assign Selected Split — Color to the Color shelf of the Marks card.
2. Adjust the color for all options available by changing the values of the Show Split By parameter.
3. Create the following calculated fields for Label.
- Ref Line — Stacked/ Side by Side
IF [Display As] IN (1, 2)
THEN [Headcount for Charts]
ENDInclude this in the Detail shelf of the Headcount for Charts marks card. Then, go to Analytics tab and Drag Reference Line to the Headcount for Charts axis and setting it to refer to the Sum of the Ref Line — Stacked/ Side by Side field for each pane. Display only the value on the label, by setting line or tooltip to None.
- Ref Line — Pos Butterfly
IF[Display As]= 3 THEN
IF [Headcount for Charts]>= 0
THEN [Headcount for Charts]
END
END- Ref Line — Neg Butterfly
IF[Display As]= 3 THEN
IF [Headcount for Charts] < 0
THEN [Headcount for Charts]
END
ENDSwitch the Display As parameter to Butterfly chart. Include both of these fields on the Detail shelf of the Headcount for Charts marks card. Next, add reference lines for each of the two fields and format them accordingly.
- % Total Employee — Label
IF [Show Split By] = 1
THEN NULL
ELSE
ABS([% Total Employee])
ENDPlace % Total Employee — Label on the Label shelf of the % Total Employee marks card and format the font color.
4. Remove all axis and header columns by unchecking Show Header and remove gridlines as well as column divider for a cleaner visualization. Set the color according to the requirements. Set the tooltip as per the requirements.
Creating the Dashboard
Set the dashboard size to a fixed 1000 px by 600 px. The following layout hierarchy outlines how the dashboard was created.
The dashboard:
Thanks for reading! Stay updated with the latest insights, ideas, and inspirations by following my profile. Keep shining with your data visualizations!