1.0 Critiques and Suggestions for Current Visualisation

1.1 Clarity

S.N	Critiques	Suggestions
1	Age bands in the chart/table is not reflective of the age bands used in the analysis/key message. For example, to obtain the share of residents aged 55 & over in the labour force, readers must manually sum the four columns “55-59”, “60-64”, “65-69” and “70 & over”.	Use 3 age bands: “15-24”, “25-54” and “55 & over”
2	Age bands (x-axis) are categorical variables, and line graph is not appropriate as it is more suitable for continuous variable. No grid line, hence difficult to map the value in the table to the point on the line.	Bar chart will be more appropriate for categorical variable, include labels beside bar for ease of reading.
3	The chart’s x-axis is in bands (categorical data), but the vertical dotted line (median age) is a numeric/continuous data. By including the vertical line, readers may mis-interpret that the intercept is the share of residents in the labour force at a particular age.	Create another chart for median age trend line.
4	Data on labour force participation rate (LFPR) by age is not available in the chart/table. Note: LFPR at the overall level (without age breakdown) is available in para 1.4 (Chart 4 of the report)	Include a chart on LFPR by age
5	Analysis is only on the snapshot of 2009 (10 years ago) and 2019. Missed out on what happened in between.	Include analysis on the snapshot of 2014 (5 years ago).

1.2 Aesthetics

S.N	Critiques	Suggestions
6	The word “June” is labelled multiple (6) times on the chart.	As the reference period for the Comprehensive Labour Force Survey is always as at June, suggest to amend the note at the bottom of the chart to “Data for each year are for June periods, and may not add up to 100% due to rounding.”
7	The colour tone of the chart’s background is very closer to the tone of the 2009 line, which makes it difficult to read the trend. Resolution of the chart is also very poor, making it even more difficult to read the trends and figures in the table.	Increase the transparency of the background colour and use a different colour from the background. Higher resolution image should be attached in the report instead.
8	The vertical dotted lines (reference line) are too thin and makes it difficult to differentiate the colour between the two lines.	Weight of the reference lines (if any) should be heavier/more
9	No annotation on the charts and hence readers may not notice any interesting findings.	Include annotation in the chart to bring out interesting findings.

2.0 Proposed Design

S.N	Advantages
1	Reduce the number of age groups to focus on the 3 main broad groups for better focus and easier to related back to the analysis.
2	Bar chart instead of line graph since age bands are categorical. By showing only 2019 data, readers can have a quick sense of the 2019 labour market (it’s a 2019 labour market report).
3	Labels are next to the bars, hence readers do not need to look around the chart or the table for the value.
4	Included a chart for participation rate by age and median age so that readers can better relate the analysis with the charts.
5	Instead of showing charts on shares/participation rate for 2009 and 2014, it will be helpful to show the change in percentage points since 2009 and 2014 respectively as readers do not need to compute the change mentally. The 2014 data also provides readers additional view of the recent 5 years.
6	Remove “June” label from the title of the charts and include additional notes at the bottom.
7	White background for better contrast with the charts presented.
8	By having separate colours for positive and negative values, readers can easily know if the change is positive or negative.

3.0 Data Visualisation Steps

3.1 Data Preparation

3.1.1 Resident Labour Force dataset

Step 1: Download “Table (7) Resident Labour Force Aged Fifteen Years and Over by Age and Sex, 2009 – 2019 (June)” from https://stats.mom.gov.sg/Pages/Labour-Force-Tables2019.aspx.

Step 2: Format the excel. Unmerge the cells and remove unnecessary rows/columns. Unmerge row 5, rename column D as “Age” and delete columns A-C, F, H, J, L, N, P, R, T, V, X and Z (i.e. empty columns). Delete rows 1-4, 6 and 19. Rename the columns to include “(old)” and save the excel file.

Step 3: Open Tableau and connect to the excel file saved out in Step 2.

A view of the table in Tableau

Step 4: Pivot the table and rename the columns to “Year” and “Counts”.

Step 5: Right click on the drop down list under “Age” column, click “Create Group” to create 3 groups “15 - 24”, “25 - 54” and “55 & over” by highlighting the subgroups and clicking on the “Group” button. Rename the group and repeat for the other 2 groups.

Step 6: Open a new worksheet in Tableau and drag “Age (group)” to Columns, “Year” to Rows and “Counts” to “Text” in the marks card. To obtain the row total, click on “Analysis” and drag “Totals” to “Rows Grand Totals”.

Step 7: To obtain the share (%), click on “Sum(Counts)” in the marks card, and click on “Quick Table Calculations” and select “Percent of Total”

Step 8: Save this table for using in the visualisation by clicking on “Analysis” at the ribbon bar, select “View Data”, followed by “Export All” and save into a folder.

3.1.2 Resident Labour Force Participation Rate dataset

Step 1: Download Labour Force Participation rate dataset from Singstat Table Builder (https://www.tablebuilder.singstat.gov.sg/publicfacing/mainMenu.action) by following the illustration below

Step 2: Open the excel file and copy the “outside labour force” table to the top right as shown below. Rename the years “In” and “Out” to reflect the counts for in and out of labour force. Delete rows 1-5, 7, 19 onward and rename column A to “Age”. Save the Excel file.

A view of the final table in Excel

Step 3: Open Tableau window and import the Excel saved in Step 2.

Step 4: Right click on the drop down list under “Age” column, click “Create Group” to create 3 groups “15 - 24”, “25 - 54” and “55 & over” by highlighting the subgroups and clicking on the “Group” button. Rename the group and repeat for the other 2 groups.

Step 5: To obtain the labour force participation counts by the newly created age bands, click to open a new Tableau workbook. Drag “Measure Names” to “Columns”, “Age (group)” to “Rows”, and “Measure Values” to the Text marks card as shown below.

Step 6: Save this table by clicking on “Analysis” at the ribbon bar, select “View Data”, followed by “Export All” and save into a folder.

Step 7: Drag the csv file saved in Step 6 into the polaris, right click the drop down list for the column “Year_In/Out” and click on Split. Rename the new columns “Year” and “In/Out”.

Step 8: Open a new Tableau workbook, drag “In/Out” into “Columns”, “Age (group)” and “Year” into “Rows” and “Counts” into the Text marks card. Click on “Analysis” and drag “Total” into “Row Grand Totals”.

Step 9: To compute participation rate (%), click on the “Analysis” ribbon and select “Percentage of” and click “Row in Pane”.

Step 10: Save this table by clicking on “Analysis” at the ribbon bar, select “View Data”, followed by “Export All” and save into a folder.

Step 11: Open the csv file saved in step 10. To get the labour force participation rate, filter the column “In/Out” and delete rows that are “Out” (“In” refers to individuals in the labour force while “Out” refers to individuals not in the labour force). Save the file.

3.1.3 Median Age of Labour Force

Step 1: Download “Table (2) Key Characteristics of Resident Labour Force, 2009 – 2019 (June)” from https://stats.mom.gov.sg/Pages/Labour-Force-Tables2019.aspx.

Step 2: Keep only columns B and G, delete rows 1-6 and 18 onward. Insert one row in row 1 and label “Year” and “Median Age”, to obtain the table below. Save the excel.

3.2 Tableau Works

3.2.1 Importing and transforming data for creation of visualisation

Step 1: Import csv file for shares of resident labour force saved in 3.1.1 (step 8).

Step 2: Remove the old dataset and drag in the share file. Delete the first two columns, and rename last column to “Share”.

Step 3: To obtain shares in %, click on the drop down under “Share” column and select “Create Calculated Field”. Type the formula shown below and click “OK”. Rename the new column as “Shares (%)”.

Step 4: Import participation rate csv file saved in 3.1.2 (step 11) by adding a connection.

Step 5: Drag in the participation rate file to create a union with the labour force share file and edit the relationship as shown below.

Step 6: To obtain the participation rate in %, click on the drop down and create a calculated field as shown below.

Step 7: For both data in step 6 and 3, change the data type for “Year” to date as shown below.

Step 8: Add new data source to import median age data obtained from 3.1.3 (step 2). Similar to step 7, change the data type for “Year” to date.

3.2.2 Creation of charts to show shares of resident labour force

Do steps 1 - 5 to create visualisation for 2019 labour force data, and steps 6 - 8 for comparison with 2009 and 2014.

Step 1: Open a new Tableau worksheet. Drag “Shares (%)” to “Columns”, “Age (group)” to “Rows” and “Year” to filter marks card. In the filter, select “Year”, then “Next” and check the box for 2019.

Step 2: Drag “Shares (%)” to the text marks card, click the green pill, select “format” to format the labeling of the bars.

Step 3: (To do formatting of the chart e.g. remove the grid lines etc) Click on the x-axis and select “format” and select “None” for the Grid lines under “Sheet”, “Columns” and “Rows”.

Step 4: Click on the x-asix and un-tick “Show Header”.

Step 5: Click on colour in the marks card and select the colour for the bar.

Step 6: Go to “Data Source” sheet and create 2 calculated fields as shown below.

Step 7: Create another 2 calculated fields to obtain the change in percentage points since 2009 and 2014.

Step 8: Open a new Tableau worksheet. Drag “Percentage Change wrt 2009” and “Percentage Change wrt 2014” into “Columns” and “Age (group)” into Rows" as shown below. Ensure that “Bar” is being selected at the marks card. Drag “Percentage Change wrt 2009” and “Percentage Change wrt 2014” into their respective Text marks card for the label to appear and format the label to show in “pp” (similar to step 2). Ensure that the range for both charts are the sam by clicking on the x-axis, then click “Edit axis”. Click on the x-axis and y-axis and un-tick “Show Header” to hide the axis. Drag “Percentage Change wrt 2009” and “Percentage Change wrt 2014” into colour marks card to format the colour of the bar as shown below.

3.2.3 Creation of Charts to show participation rate

Repeat the steps in 3.2.2 (steps 1 - 5) to create visualisation for 2019 participation rate, and the steps in 3.2.2 (steps 6 - 8) for comparison with 2009 and 2014.

Step 1 - 5: Repeat the steps in 3.2.2 (steps 1 - 5) for participation rate data and the final visualisation is as below

Step 6 - 7: Repeat the steps in 3.2.2 (steps 6 - 7) with the following formula

Step 8: Repeat the steps in 3.2.2 (step 8) and the final visualisation is as below

3.2.4 Creation of Charts to show median age trend

Step 1: Open a new Tableau worksheet and select data source “Median Age”. Drag “Year” into “Columns”, “Median Age” into “Rows” and Text marks card to obtain the following chart (ensure to select Line in the marks card)

4.0 Final Data Visualisation Output

Data Visualisation Link (Tableau Online)

https://public.tableau.com/views/DataVizMakeoverSingaporesResidentLabourForcein2019/FinalDashboard?:language=en&:display_count=y&:origin=viz_share_link

Major Insights

S.N	Insights
1	Share of older residents (aged 55 & over) in the labour force rose by 9pp compared to a decade ago to 25%, while share of residents aged 25 - 54 declined by 8pp.
2	Despite a decline in labour force share, labour force participation rate of aged 25 - 54 rose by 4pp compared to a decade ago to 88%.
3	Share of residents in the labour force by age and participation rate by age were relatively stable over the last 5 years (2014 - 2019).
4	Median Age of residents in the labour force increased from 41 years old in 2009 to 44 years old in 2019. Nonetheless, the median age remained at 43 years old from 2014 to 2018 before increasing to 44 years old in 2019.

DataViz Makeover 01