• Introduction
• Prerequisites
• Step 1: Create and configure the data model
  • Import the data
  • Transform "Birth Date" into age (optional)
  • Grouping ages by group
  • Configure the data model
    • Change the type of the Age column
    • Create the measures Number of women and Number of men
• Step 2: Configuring the chart Age pyramid
  • Create the Bar chart
  • Change the age sorting
  • Remove negative display
• Congratulations!

Introduction

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.

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".

This entire tutorial takes place in the Studio.
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 :

• have installed DigDash Enterprise version 2025R1 or later;
• be a member of the"Data Model Designer" authorisation group;
• download the Excel file "Employee_Register.xlsx".

Step 1: Create and configure the data model

Import the data

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.
2. Open the Templates tab.
3. Click on the New Template button.
4. In the Create a new data model box, select All types in the Files section.
   ➡ The Search for remote files box appears.
5. In the Server drop-down list, select "Common Datasources".
6. Click on the Add a file to the server... button.
7. The Select a local file or URL box appears, keep the default selection From your computer.
8. Click Browse to select the "Employee_Register" file retrieved earlier.
9. Click OK.
   ➡ The file is now saved on the DigDash"Common Datasources" server and accessible to all users.
10. In the Search for remote files box, select "Registre_RH".
11. Click OK.

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

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.
To do this:

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

Transform "Birth Date" into age (optional)

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.
2. Click Add to create a new data transformation.
   ➡ The Create Transformation interface is displayed.
3. Select the Birth Date column in the Target column drop-down list.
   
4. In the Applied function field , click the  button to display the list of functions, or type Age directly in the search field.
   
5. Select the Age function and click Apply.
   ➡ The calculated age is displayed in the Birth date column.
   
6. Click the header and then select Rename to change the name of the column (Age).
7. Click on the  button to finish.

Grouping ages by group

Here we have chosen to group the ages in groups of 5.
To do this:

1. Open the Data transformation tab.
2. Click Add to create a new data transformation.
   ➡ The Create Transformation interface is displayed.
3. Select the Age column from the Target column drop-down list.
4. Enter the following script in the Script Editor:

4. Click Apply.
   ➡ The Age column is transformed.
   

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.

Configure the data model

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

Change the type of the Age column

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

1. Select the Age column.
2. In the Type field at the top right, select Dimension.
   
3. Also change the type of the following columns:
   • Postcode: select Dimension (geographic)
   • Nationality : select Dimension

Create the measures Number of women and Number of men

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.

1. Click the New Measure button at the top left of the list of columns.
2. In the window Calculated measure window that appears, enter the name of the new measure: Number of men.
3. Then enter the following formula (double-click on the Employee ID dimension to insert it in the formula):

4. Edit the dimension to add a filter (right-click and Edit).
   ➡ The associated Dimension "Employee ID" box is displayed.
5. In the Add dimension drop-down list, select the dimension Gender.
6. In the Filter column, click Off and then New.
   ➡ The Edit filter for dimension Gender dialog box is displayed.
7. Keep the filter type Elements and tick M for men.
   
8. Click OK.
9. Repeat the operation for the number of women without the negative sign and selecting F at the filter step.
   ➡ The 2 new measures Number of men and Number of women are added to the list of columns.
10. Click Finish and enter a name for the model: Age pyramid to save.

Step 2: Configuring the chart Age pyramid

Create the Bar chart

1. In the Flows tab of the Studio, click the New flow button.
2. Select the Bar chart.
   ➡ The Flow Properties window appears.
3. Select the data model previously created : Age pyramid.
4. Drag and drop the Age dimension onto the Bar axis (y-axis).
5. 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.
6. On the toolbar, click Colors and then the Edit button to change the color palette.
7. Select the palette Color gender.
   
    

Change the age sorting

To display the ages in the desired order :

1. Click the Sort button at the bottom right of the Bar axis.
2. Select Sort: Numeric Descending.
   
3. Click OK.

Remove negative display

To remove the negative display for the values on the left :

1. Go to the Scales tab.
2. Change the horizontal scale format using the .
   
   ➡ The Format Editor appears.
3. Click + to create a new format.
4. Enter the name Number (positive) and click OK.
5. Then select Absolute value in the Formatting a negative number drop-down list and click OK.
   
   ➡ This gives a positive scale on both sides.
6. You can select the Center at zero check box to see the differences clearly.
   
7. Enter the desired name for the chart and click OK to save.

The age pyramid is now ready to be used in a dashboard.

Congratulations!

You've successfully created an age pyramid.
Now all you have to do is apply it to your data!