• Introduction
• Prerequisites
• Overview of methods
• Step 1: Create and configure data models
  • Register_RH model
    • Import the "Register_RH" data
    • Configuring the data model
  • HR_Contracts template
    • Importing "HR_Contracts" data
    • Calculate the FTE
  • Complete_HR data model
  • Calendar data model
• Applying the "Cartesian product" method
  • Step 2: Perform a join of the Calendar and HR_Complete data models
    • Modifying the Calendar template
    • Modifying the HR_Complete model
    • Joining the HR_Complete and Calendar models
  • Step 3: Calculate the sum of FTEs
  • Step 4: Create a table Sum of FTEs by department.
• Application of the "HR_Complete" model method alone
  • Step 2: Create the FTE N, FTE N-1 and FTE N & N-1 measures
    • ETP N measurement
    • N-1 FTE measure
    • FTE N & N-1 measure
  • Step 3: Create the ETP gained, lost and stable measures
    • FTE earned
    • FTE lost measure
    • Stable ETP measure
  • Step 4: Check the result
• Congratulations!

Introduction

In this tutorial, we will look at how to calculate the number of FTEs in Digdash.
The FTE OR Full Time Equivalent is a unit of measurement that represents the work of a full-time employee. It can be used as a basis for other calculations such as the average number of employees.

To do this, we will use several fictitious data sets:

• The Excel file Registre_RH.xls containing information on employees such as their ID, surname, first name, age, etc.
• The CSV file Contracts_RH containing employee identifiers, their leaving date and the number of days worked; the latter data is considered on a weekly basis.
• The Calendar_2020_2025 CSV file containing calendar data, consisting of a single column with dates from 01/01/2020 to 31/12/2025.

Prerequisites

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 files "Registre_RH", "Contrats RH.csv" and "Calendrier_2020_2025.csv" in the zip file  ETP_tuto_files.zip.

Overview of methods

There are two ways of dealing with this subject, each with its own advantages and disadvantages:

• The first consists of creating a Cartesian product between our two datasets. The advantage of this is that we can keep all the data and therefore retain interactivity with the dimensions (for example, filtering on a date). The disadvantage is the volume of data. Here we have: number of rows of HR data (305) x number of calendar rows (2191), i.e. 668,255 records.
  
• The second solution is based solely on HR data and allows predefined data to be displayed (for example: FTEs over a year, a month or a predefined period). The advantage is a limited volume of data, but on the other hand, the interactivity of the dashboard will be more limited. It will not be possible to filter by date.

Step 1: Create and configure data models

Before you can work with the methods described above, you need to integrate the various data into Digdash.

Register_RH model

Import the "Register_RH" data

Here we are going to import the data from the Excel file "Registre_RH" (retrieved earlier) which represents the human resources data of 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 "Regist_RreH_reformaté" 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_reformaté".
11. Click OK.

The Excel File box appears. 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.
  

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.

Configuring the data model

The data model configuration window opens on the Columns tab.

The type detected for some columns is not correct.
The Age column was detected as a measure. 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 Postcode column: select Dimension (geographical).

You can then save:

• Click Finish and enter a name for the HR_Register template.

HR_Contracts template

Importing "HR_Contracts" data

Here we are going to import the data from the csv file "Contrats_RH" containing the employee's identifier, their leaving date and the number of days worked.

To do this

1. Create a new template in the same way as before and add the Contrats_RH.csv file to the document server.
   ➡ The Excel File box appears with the data preview.
2. In the Data selection section, tick the First row as header box .

Calculate the FTE

We are now going to calculate the Full Time Equivalent (FTE) for each employee from the number of days worked per week using a data transformation. To do this

1. Add an empty column.
2. Click on the column header and then, in the context menu, on Data transformation.
     
   ➡ The Data transformation tab opens and displays the interface for creating a transformation with the target column selected.
3. In the script editor, enter the following code:

Here, values[2] corresponds to the "No. of days worked" column.

5. Click Apply.
   ➡ You can view the result obtained in the column preview.
6. Click on the  button to finish.
7. Click on the header of Column 3 to rename it: enter Nb FTE.
   ➡ The result is as follows:
   

You can now save:

• Click Finish and enter a name for the template: HR_Contracts.

Complete_HR data model

We are now going to combine the data from the "HR_Register" and "HR_Contracts" models previously created by performing a join.
The join consists of aggregating the columns from several models thanks to at least one column match called the join key.
You can consult the page  Performing a data join page for more details.

1. As before, click on the New model button.
2. In the Create a new data model box, select Join in the Other section.
   ➡ The Join dialog box appears.
3. Click on the + button to the right of the Selected data sources section and select the Register_RH data model.
4. Repeat the operation to select the Contracts_RH data model.
5. In the Key columns section, tick the Employee ID column to use it as the join key.
   
6. You can click Next to view the list of columns after the join.
7. Click Finish and name the HR_Complete data model to save.

Calendar data model

We are now going to create the "Calendar" data model based on the "Calendar_2020_2025.csv" file.

To do this

1. Create a new model in the same way as before and add the "Calendar_2020_2025.csv" file to the document server.
   ➡ The Excel File box is displayed with the data preview.
2. In the Data selection section, tick the First line as header box .
    
   
3. Click Finish and enter a name for the template: Calendar.

Applying the "Cartesian product" method

In order to create a Cartesian product between our two data models, "Calendar" and "HR_Complete", we need to have a common column with identical values in both models. To do this, we're going to modify the two models we created earlier and add a column that will serve as the join key.

Step 2: Perform a join of the Calendar and HR_Complete data models

Here we are going to combine the data from the Calendar and HR_Complete models. To do this, we first need to modify the models so that we have a join key.

Modifying the Calendar template

1. Edit the Calendar data model.
2. Add an empty column.
3. Open the Data transformation tab ...
4. Click Add to create a new data transformation.
   ➡ The Create Transformation interface is displayed.
5. Select Column1 from the Target Column drop-down list.
6. Enter the following code in the script editor:
   return 'a';
7. Click Apply and then the  button to finish.
8. Click on the column header then Rename to give the column the name Join. 
   
9. Click Finish to save.

Modifying the HR_Complete model

We are going to modify the HR_Complete model via the HR_Contracts model.

1. Edit the HR_Contracts data model .
2. Add a second column.
3. Carry out the same operations as in the previous paragraph (Calendar template).
4. Click on Finish to save.
   ➡ A message warns you that the dependent models have been updated.
   
5. Click OK.
   ➡ In this way, the column is moved up to the "Complete HR" template:
   

Joining the HR_Complete and Calendar models

We are now going to combine the HR_Complete and Calendar models by performing a join.

1. Create a new Join data model as described in the paragraph HR_Complete data model.
2. Add the RH_Complete model and then the Calendar model.
3. Tick the Join column in the Key columns section .
   

Step 3: Calculate the sum of FTEs

To continue, on the next screen we are going to create a calculated measure giving us the sum of the FTEs:

1. Click Next to display the list of columns.
2. Click on the New measure button and then on Calculated measure (advanced user).
   
3. Enter the following code:

4. Enter the name of the measurement: Sum FTE.
5. Uncheck the Calculation after aggregation box.
6. Click OK.
   ➡ The measure is added to the list of columns.
7. Click Finish and name the ETP model Cartesian product.

Step 4: Create a table Sum of FTEs by department.

We can now create a table displaying the sum of FTEs by department to visualise the result.

1. From the Templates tab, click on the New Flow button.
2. In the Create a graphical or document builder flow box, select Table.
3. Select the Cartesian product FTE data model .
4. Drag and drop the Service dimension and then the Sum FTE measure.
5. Filtering on the date 01/01/2023, you should obtain the following table:
   
   

Application of the "HR_Complete" model method alone

For this second method, we are going to work on the FTEs gained, lost and stable over the current year. In this example, we will obtain the results as at today's date.

To do this, we need to determine three values: the FTEs for year N, year N-1 and the total of the two. These data will enable us to find out the number of FTEs for the current year, as well as any variations (new employees and leavers). We are therefore going to create 3 new measures calculated in the "Complete HR" model.

Step 2: Create the FTE N, FTE N-1 and FTE N & N-1 measures

1. Edit the HR_Complete data model.
2. Click Next to go to the Columns tab.
3. Create a new calculated measure (advanced user).

ETP N measurement

To calculate the FTEs for year N :

1. Enter the following code:

2. Enter the name of the measure: ETP N.
3. Uncheck the box Calculation after aggregation.
   
4. Click on OK.

N-1 FTE measure

To calculate the FTEs for year N-1 :

1. Enter the following code:

2. Enter the name of the measure: ETP N-1.
3. Uncheck the box Calculation after aggregation.
   
4. Click on OK.

FTE N & N-1 measure

To calculate the total FTE for year N and N-1 :

1. Enter the following code:

2. Enter the name of the measure: FTE N & N-1.
3. Uncheck the box Calculation after aggregation.
   
4. Click on OK.
5. Click Finish to save the changes.

Step 3: Create the ETP gained, lost and stable measures

From here, all that remains is to calculate the differences between these values to obtain the desired measures:

• FTEs gained represent new entrants in the current year
• FTEs lost represent those leaving between the previous year and the current year
• The stable FTEs represent the others who have not moved.

To do this, we will create 3 new measures calculated in the same way as above, with the following properties.

FTE earned

1. Enter the following code:

2. Enter the name of the measure: ETP gained.
3. Leave the Calculation after aggregation box ticked.
4. Click on OK.

FTE lost measure

1. Enter the following code:

2. Enter the name of the measure: FTE lost.
3. Leave the box Calculation after aggregation ticked.
4. Click on OK.

Stable ETP measure

1. Enter the following code:

2. Enter the name of the measure: Stable FTE.
3. Leave the Calculation after aggregation box ticked.
4. Click on OK.

You should obtain the following columns:

Click on Finish to save.

Step 4: Check the result

To check the result, we are going to create a table showing the FTEs gained, lost and stable by department.

1. From the Templates tab, click on the New Flow button and then select Table.
2. Select the HR_Complete data model .
3. Drag and drop the Department dimension and then the 3 measures FTE gained, FTE lost and FTE stable.
   ➡ You should obtain the following result. We can see that during this year, we have only lost employees and there have been no new hires. This is normal given the dates in our dataset.
   
4. Rename the Flow and click OK to save.

Congratulations!

You have successfully created an FTE tracker.
Now all you have to do is apply it to your data!