Creating an age pyramid

The aim of this tutorial is to create an age pyramid. A population pyramid is used to represent the gender and age distribution of a population at a given time.

17

18

The dataset used in the tutorial corresponds to fictitious data from a company containing information about employees such as their ID, name, age, etc. This data is contained in the Excel file "**[[Employee_Register.xlsx>>attach:Employee_Register.xlsx]]**".

19

20

This entire tutorial takes place in the Studio.

21

To create the age pyramid, we will first prepare the data and then configure a Bar chart.

= Prerequisites =

In order to complete this tutorial, you will need to :

26

27

* have installed DigDash Enterprise version 2025R1 or later;

28

* be a member of the**"Data Model Designer**" authorisation group;

29

* download the Excel file "**[[Employee_Register.xlsx>>attach:Employee_Register.xlsx]]**".

(% class="box" %)

(((

ℹ The screenshots in this tutorial were produced using the Chrome browser. There may be slight differences depending on the browser.

34

)))

35

36

= Step 1: Create and configure the data model =

37

38

(% class="box infomessage" %)

39

(((

40

This tutorial assumes that you will import your data sources and create your graphs in your personal role.

41

)))

42

43

== Import the data ==

44

45

Here we're going to import data from the Excel file "Employee_Register.xlsx" (retrieved earlier), which represents the human resources data for a fictitious company.

To do this:

1. Launch the **Studio**.

50

1. Open the **Templates** tab.

51

1. Click on the **New Template** button.

52

1. In the **Create a new data model** box, select **All types** in the **Files** section.

53

➡ The **Search for remote files **box appears.

54

1. In the **Server** drop-down list, select "**Common Datasources**".

55

1. Click on the **Add a file to the server...** button.

56

1. The **Select a local file or URL **box appears, keep the default selection** From your computer**.

57

1. Click **Browse **to select the **"Employee_Register" **file retrieved earlier.

58

1. Click **OK**.

59

➡ The file is now saved on the DigDash**"Common Datasources"** server and accessible to all users.

60

1. In the **Search for remote files** box, select **"Registre_RH"**.

61

1. Click **OK.**

62

63

The **Excel File **box is displayed. It offers data selection options and a preview of the data.

64

65

[[image:Data_preview_EN.png]]

66

67

The items in the first row of the table correspond to the data types in each column. We will therefore use them as column headings. For example, Employee ID for column 1.

68

To do this:

69

70

* In the **Data selection** section, select the **First row as header** checkbox.

71

[[image:Data_selection_EN.png||alt="Data selection"]]

72

73

== Transform "Birth Date" into age (optional) ==

74

75

If you only have the date of birth, you can transform the data to obtain the age.

To do this:

1. Open the **Data transformation** tab.

80

1. Click **Add **to create a new data transformation.

81

➡ The **Create Transformation** interface is displayed.

82

1. Select the //Birth// //Date //column in the **Target column** drop-down list.

83

[[image:Data_tarnsform_add_EN.png||alt="Data transform"]]

84

1. In the **Applied function **field **, **click the [[image:Function_button_FR.png]] button to display the list of functions, or type //Age// directly in** **the search field.

85

[[image:Age_function_search_EN.png||alt="Age"]]

86

1. Select the **Age **function and click **Apply**.

87

➡ The calculated age is displayed in the //Birth date// column.

88

[[image:Age_function_applied_EN.png]]

89

1. Click the header and then select **Rename **to change the name of the column (Age).

90

1. Click on the [[image:Close_transfo_edit_button.png]] button to finish.

91

92

== Grouping ages by group ==

93

94

(% class="box infomessage" %)

95

(((

96

💡 Several transformations can be applied to the same column, so you can group the ages in the Age column resulting from the transformation of the Birth date column.

97

Pay attention to the order in which the data transformations are applied.

98

)))

99

100

Here we have chosen to group the ages in groups of 5.

101

To do this:

102

103

1. Open the **Data transformation** tab.

104

1. Click **Add **to create a new data transformation.

105

➡ The **Create Transformation** interface is displayed.

106

1. Select the //Age// column from the **Target column** drop-down list.

107

1. Enter the following script in the Script Editor:

var v = value;

while(v%5 != 0) {

v++;

}

return v +'';

[[image:Age_function_group_EN.png||alt="Age groups"]]

(% start="4" %)

1. Click **Apply**.

➡ The **Age** column is transformed.

122

[[image:1751037537297-627.png||alt="Result"]]

123

124

We can now move on to configuring the data model: click the **Next **button at the bottom right to open the data model configuration window.

125

126

== Configure the data model ==

127

128

The data model configuration window opens on the **Columns** tab. We are going to make the necessary settings in the data model.

129

130

=== Change the type of the Age column ===

131

132

The **Age** column has been detected as a measurement. However, it will be used here as a dimension. To change this:

133

134

1. Select the** Age** column.

135

1. In the **Type** field at the top right, select **Dimension**.

136

[[image:Age_dimension_EN.png||alt="As as dimension"]]

137

1. Also change the type of the following columns:

138

1*. **Postcode**: select **Dimension (geographic)**

139

1*. **Nationality **: select **Dimension**

140

141

=== Create the measures Number of women and Number of men ===

142

143

We need to know the number of women and men for each age group. To do this, we are going to create 2 calculated measures based on the unique identifier determining the person. The calculated measure displayed on the left of the axis must be negative. In this example, we choose to place men on the left and women on the right of the age pyramid.

144

145

1. Click the **New Measure **button at the top left of the list of columns.

146

1. In the window** [[Calculated measure>>doc:dev:Digdash.user_guide.studio.Create_datamodel.configure_data_model.Configure_columns.Create_new_measure.Create_calculated_measure.WebHome]]**[[ >>doc:dev:Digdash.user_guide.studio.Create_datamodel.configure_data_model.Configure_columns.Create_new_measure.Create_calculated_measure.WebHome]]window that appears, enter the name of the new measure: **Number of men**.

147

1. Then enter the following formula (double-click on the** Employee ID** dimension to insert it in the formula):

148

149

150

return -<ID Employé(dcount)>;

151

152

153

[[image:Measure_men_number_EN.png||alt="Men number" height="456" width="772"]]

154

155

(% start="4" %)

156

1. Edit the dimension to add a filter (right-click and **Edit**).

157

➡ The **associated Dimension "//Employee ID" //**box is displayed.

158

1. In the **Add dimension** drop-down list, select the dimension **Gender**.

159

1. In the **Filter** column, click **Off** and then **New**.

160

➡ The **Edit filter for dimension Gender **dialog box is displayed.

161

1. Keep the filter type **Elements** and tick **M** for men.

162

[[image:Edit_measure_men_number_EN.png||alt="Edit filter"]]

163

1. Click **OK**.

164

1. Repeat the operation for the number of women without the negative sign and selecting **F **at the filter step.

165

➡ The 2 new measures **Number of men **and **Number of women **are added to the list of columns.

166

1. Click **Finish **and enter a name for the model:** Age pyramid **to save.

167

168

= Step 2: Configuring the chart Age pyramid =

169

170

== Create the Bar chart ==

171

172

1. In the **Flows **tab of the **Studio,** click the** New flow** button**.**

173

1. Select the **Bar** chart.

174

➡ The **{{glossaryReference glossaryId="Glossary" entryId="Flux"}}Flow{{/glossaryReference}} Properties** window appears.

175

1. Select the data model previously created : **Age pyramid**.

176

1. Drag and drop the **Age **dimension onto the **Bar **axis (y-axis).

177

1. Drag and drop the 2 measures **Number of men **(negative) and **Number of women **(positive) onto the **Stacking **axis (x-axis). The order is important.

178

1. On the toolbar, click **Colors **and then the** Edit **button to change the [[color palette>>doc:dev:Digdash.user_guide.studio.Create_flow.Configure_flow.Flow_toolbar.Colors.WebHome]].

179

1. Select the palette **Color gender**.

180

[[image:Bar_chart_configuration_EN.png||alt="Bar chart"]]

181

182

183

== Change the age sorting ==

184

185

To display the ages in the desired order :

186