• Introduction
• Prerequisites
• Step 1: Create and configure the data model
  • Import the "Registre_RH" data
  • Transform date of birth into age (optional)
  • Group ages by group
  • Configure data model
    • Change the type of the Age column
    • Create the measures Number of women and Number of men
• Step 2: Configuring the Age Pyramid chart
  • Create the Bars graph
  • Change the age sorting
  • Suppress 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 Registre_RH.xls 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 "Registre_RH".

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 2024R1 or later;
• be a member of the"Data Model Designer" authorisation group;
• download the Excel file "Registre_RH".

Step 1: Create and configure the data model

Import the "Registre_RH" data

Here we're going to import data from the Excel file "Registre_RH" (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 local file or URL box appears, keep the default selection From your computer.
8. Click Browse to select the "Registre_RH " 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, tick the First row as header box.
  

Transform date of birth into age (optional)

If you only have the date of birth, you can perform a data transformation to obtain the age.

To do this

1. Click on the Birth column header.
2. In the pop-up menu which appears, select Data transformation...
   
3. In the Type field of the dialog box which appears, select Shared function.
4. In the list of Shared functions, select Age.
   
5. Click OK.
   ➡ The date of birth is replaced by the calculated age.
6. Click on the header and then select Rename to change the name of the column.

Group ages by group

Here we choose to group ages by groups of 5.
To do this:

1. Click on the Age column header.
2. In the pop-up menu which appears, select Data transformation...
3. Perform a data transformation on the Age column.
   
4. In the " Age" column Transformation box that appears, select the Script type and enter the following script:

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

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

Configure 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 (geographical)
   • Entry date : select Dimension (time)

Create the measures Number of women and Number of men

We need to know the number of women and men for each age. 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 on 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 "Dimension name " box is displayed.
5. In the Add a dimension drop-down list, select the Gender dimension.
6. In the Filter column, click Off and then New.
   ➡ The Edit filter for the Gender dimensionbox is displayed.
7. Keep the Elements type and tick H for men.
   
8. Click on OK.
9. Repeat the operation for the number of women without the negative sign and selecting F in 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: Pyramid ages to save.

Step 2: Configuring the Age Pyramid chart

Create the Bars graph

1. From the Flow tab in the Studio, click on the Flow button.
2. Select the Bars flow.
   ➡ The Flow Properties window appears.
3. Select the data model created earlier: Pyramid ages.
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).
6. From the toolbar, click on Colours and then the Edit button to change the colour palette.
7. Select the Color gender palette.
   

Change the age sorting

To display the ages in the desired order :

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

Suppress negative display

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

1. Go to the Scales tab.
2. Edit the format of the lower scale using the .
   
   ➡ TheFormat Editor appears.
3. Click + to create a new format.
4. Enter the name Number (positive ) and click OK.
   
5. Then select Absolute value forNegative number display and click OK.
   ➡ This gives us a positive scale on both sides.
6. You can also tick the Centre on zero box to see the differences clearly.
   
7. Change the name of the Flow as required and click OK.

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!