• Introduction
• Prerequisites
• Overview of methods
• Step 1: Create and configure data models
  • Model Employee_Register
    • Import the data "Employee_Register" data
    • Configure the data model
  • Model HR_Contracts
    • Import data "HR_Contracts"
    • Calculate the FTE
  • Data model HR_Complete
  • Data model Calendar
• Application of the "Cartesian product" method
  • Step 2: Perform a join of the data models Calendar and HR_Complete data models
    • Modifying the model Calendar
    • Modify the model HR_Complete
    • Join the data models HR_Complete and Calendar
  • Step 3: Calculate the sum of FTEs
  • Step 4: Create a table FTEs by department
• Application of the "data model "HR_Complete" alone" method
  • Step 2: Create the measures FTE N, FTE N-1 and FTE N & N-1
    • Measure ETP N
    • Measure FTE N-1
    • Measure FTE N & N-1
  • Step 3: Create measures gained, lost and stable FTE
    • Measure Gained FTE
    • Measure Lost FTE
    • Measure Stable FTE
  • 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 "Employee_Register" containing information on employees such as their ID, surname, first name, age, etc.
• The CSV file "HR_Contracts" containing employee identifiers, their leaving date and the number of days worked; the latter data is considered on a weekly basis.
• The CSV file "Calendar_2020_2025" 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" authorization group;
• download the files "Employee_Register", "HR_Contracts.csv" and "Calendar_2020_2025.csv" in the zip file  FTE_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 data into Digdash.

Model Employee_Register

Import the data "Employee_Register" data

Here we are going to import the data from the Excel file "Employee_Register" which represents the human resources data of a fictitious company.

To do this:

1. Launch the Studio.
2. Open the Models tab.
3. Click the New model button.
4. In the Create a new data model box, select All types in the Files section.
   ➡ The Search remote files box appears.
5. In the Server drop-down list, select "Common Datasources."
6. Click the Add file... 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 "Common Datasources" server and accessible to all users.

10. In the Search remote files box, select "Employee_Register".
11. Click OK.

The Excel File window 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, select the First row as header checkbox.
  

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.

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 model: Employee_Register.

Model HR_Contracts

Import data "HR_Contracts"

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

To do this:

1. Create a new model in the same way as before and add the HR_Contracts.csv file to the document server.
   ➡ The Excel File box appears with the data preview.
2. In the Data selection section, select 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 the column header and then, on the context menu, Data Transform...
    
   ➡ The Data Transform tab opens and displays the interface to create a transformation with the target column selected.
3. In the script editor, enter the following code:

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

5. Click Apply.
   ➡ You can view the result obtained in the column preview.
6. Click the  button to finish.
7. Click 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 model: HR_Contracts.

Data model HR_Complete

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

1. Click 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 the + button to the right of the Selected Data Sources section and select the data model Employee_Register.
4. Repeat the operation to select the HR_Contracts data model.
5. In the Key columns section, check 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 enter a name for the data model: HR_Complete.

Data model Calendar

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

To do this:

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

Application of 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 be used as join key.

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

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

Modifying the model Calendar

1. Edit the data model "Calendar".
2. Add an empty column.
3. Open the Data Transform... tab.
4. Click Add to create a new data transformation.
   ➡ The interface Creating the transformation 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 the column header then Rename to give the column the name Join. 
   
9. Click Finish to save.

Modify the model HR_Complete

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

1. Edit the data model HR_Contracts.
2. Add a second column.
3. Carry out the same operations as in the previous section to get the Join column.
4. Click 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 model "HR_Complete":
   

Join the data models HR_Complete and Calendar

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

1. Create a new Join data model as described in the paragraph Data model HR_Complete.
2. Add the model HR_Complete model and then the model Calendar.
3. Check 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 the sum of the FTEs:

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

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

Step 4: Create a table FTEs by department

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

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

Application of the "data model "HR_Complete" alone" method

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 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 data model "HR_Complete".

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

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

Measure ETP N

To calculate the FTEs for year N :

1. Enter the following code:

2. Enter the name of the measure: FTE N.
3. Uncheck the box Compute after aggregation.
   
4. Click OK.

Measure FTE N-1

To calculate the FTEs for year N-1:

1. Enter the following code:

2. Enter the name of the measure: FTE N-1.
3. Uncheck the box Compute after aggregation.
   
4. Click OK.

Measure FTE N & N-1

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 Compute after aggregation.
   
4. Click OK.
5. Click Finish to save the changes.

Step 3: Create measures gained, lost and stable FTE

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

• Gained FTE represents new entrants in the current year
• Lost FTE represents those leaving between the previous year and the current year
• Stable FTE represents 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.

Measure Gained FTE

1. Enter the following code:

2. Enter the name of the measure: Gained FTE.
3. Leave the Compute after aggregation box checked.
4. Click OK.

Measure Lost FTE

1. Enter the following code:

2. Enter the name of the measure: Lost FTE.
3. Leave the box Compute after aggregation checked.
4. Click OK.

Measure Stable FTE

1. Enter the following code:

2. Enter the name of the measure: Stable FTE.
3. Leave the Compute after aggregation box checked.
4. Click OK.

You should obtain the following columns:

Click Finish to save.

Step 4: Check the result

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

1. From the Flows tab, click on the New flow button and then select Table.
2. Select the data model HR_Complete.
3. Drag and drop the dimension Department and then the 3 measures Gained FTE, Lost FTE and Stable FTE.
   ➡ You should obtain the following result. We can see that during this year, we have lost no employees and there have been no new hires. This is normal given the dates in our dataset.
   
   If we were in 2024, we would obtain the following result : no new employees but some left.
   
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!