Wiki source code of Calculer les Équivalents Temps Plein (ETP)

Last modified by Aurelie Bertrand on 2025/07/17 09:36

version	line-number	content
30.1	1	(% class="box infomessage" %)
	2	(((
129.1	3	🙋 This tutorial is intended for advanced users.
31.1	4
129.1	5	⏱ It is designed to be followed independently in 2 hours.
30.1	6	)))
	7
31.1	8	----
30.1	9
29.4	10	{{ddtoc/}}
	11
	12	----
	13
1.1	14	(% style="line-height: 1.2; text-align: justify;" %)
	15	= Introduction =
	16
129.1	17	In this tutorial, we will look at how to calculate the number of FTEs in Digdash.
	18	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.
1.1	19
129.1	20	To do this, we will use several fictitious data sets:
1.1	21
129.1	22	* The Excel file "Employee_Register" containing information on employees such as their ID, surname, first name, age, etc.
	23	* 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.
	24	* The CSV file "Calendar_2020_2025" containing calendar data, consisting of a single column with dates from 01/01/2020 to 31/12/2025.
1.1	25
	26	(% style="line-height: 1.32; text-indent: 0.153543pt; text-align: justify; background-color: rgb(255, 255, 255); margin-top: 20px; padding: 0pt 0pt 8pt;" %)
129.1	27	= Prerequisites =
1.1	28
129.1	29	To complete this tutorial, you will need to :
1.1	30
129.1	31	* have installed DigDash Enterprise version 2025R1 or later;
	32	* be a member of the "Data Model Designer" authorization group;
	33	* download the files "Employee_Register", "HR_Contracts.csv" and "Calendar_2020_2025.csv" in the zip file [[ FTE_files.zip>>attach:FTE_files.zip]].
1.1	34
2.1	35	(% class="box" %)
	36	(((
129.1	37	ℹ The screenshots in this tutorial were produced using the Chrome browser. There may be slight differences depending on the browser.
2.1	38	)))
	39
	40	(% style="line-height: 1.2; text-align: justify;" %)
129.1	41	= Overview of methods =
2.1	42
129.1	43	There are two ways of dealing with this subject, each with its own advantages and disadvantages:
2.1	44
129.1	45	* 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.
	46	* 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.
2.1	47
5.1	48	(% style="line-height: 1.2; text-align: justify;" %)
129.1	49	= Step 1: Create and configure data models{{id name="Step1"/}} =
5.1	50
129.1	51	Before you can work with the methods described above, you need to integrate the data into Digdash.
5.1	52
46.2	53	(% class="box infomessage" %)
	54	(((
129.1	55	ℹ This step is common to both methods.
46.2	56	)))
	57
129.1	58	== Model Employee_Register ==
5.1	59
129.1	60	=== Import the data "Employee_Register" data ===
6.4	61
129.1	62	Here we are going to import the data from the Excel file "Employee_Register" which represents the human resources data of a fictitious company.
5.1	63
129.1	64	To do this:
5.1	65
129.1	66	1. Launch the Studio.
	67	1. Open the Models tab.
	68	1. Click the New model button.
	69	1. In the Create a new data model box, select All types in the Files section.
	70	➡ The Search remote files box appears.
	71	1. In the Server drop-down list, select "Common Datasources."
	72	1. Click the Add file... button.
	73	[[image:Search_remote_files_dialog_EN.png\|\|alt="Search remote files"]]
	74	1. The Select a local file or URL box appears, keep the default selection From your computer.
	75	1. Click Browse to select the "Employee_Register" file retrieved earlier.
	76	1. Click OK.
	77	➡ The file is now saved on the "Common Datasources" server and accessible to all users.
5.1	78
	79	(% class="box infomessage" %)
	80	(((
129.1	81	ℹ If the //UserDocs //document server is selected the documents are only accessible to the user who uploaded them.
5.1	82	)))
	83
	84	(% start="10" %)
129.1	85	1. In the Search remote files box, select "Employee_Register".
	86	1. Click OK.
5.1	87
129.1	88	The Excel File window appears. It offers data selection options and a preview of the data.
5.1	89
129.1	90	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:
5.1	91
129.1	92	* In the Data selection section, select the First row as header checkbox.
	93	[[image:Preview_register_EN.png\|\|height="622" width="1253"]]
6.2	94
67.1	95	(% class="wikigeneratedid" %)
129.1	96	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.
67.1	97
129.1	98	=== Configure the data model ===
6.2	99
129.1	100	The data model configuration window opens on the Columns tab.
6.2	101
129.1	102	The type detected for some columns is not correct.
	103	The Age column was detected as a measure. However, it will be used here as a dimension. To change this:
6.2	104
129.1	105	1. Select the Age column.
	106	1. In the Type field at the top right, select Dimension.
	107	1. Also change the type of the Postcode column: select Dimension (geographical).
6.2	108
129.1	109	You can then save:
6.2	110
129.1	111	* Click Finish and enter a name for the model: Employee_Register.
6.4	112
129.1	113	== Model HR_Contracts ==
6.4	114
129.1	115	=== Import data "HR_Contracts" ===
6.4	116
129.1	117	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.
6.4	118
129.1	119	To do this:
6.4	120
129.1	121	1. Create a new model in the same way as before and add the HR_Contracts.csv file to the document server.
	122	➡ The Excel File box appears with the data preview.
	123	1. In the Data selection section, select the First row as header box .
6.4	124
129.1	125	=== Calculate the FTE ===
6.10	126
129.1	127	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:
6.4	128
129.1	129	1. Add an empty column.
	130	1. Click the column header and then, on the context menu, **Data Transform...
	131	[[image:Data_transform_EN.png\|\|alt="Data transform" height="584" width="1185"]] **
	132	➡ The Data Transform tab opens and displays the interface to create a transformation with the target column selected.
	133	1. In the script editor, enter the following code:
6.4	134
6.7	135	{{code}}
	136	if(values[2])
129.1	137	return values[2]/5;
6.7	138	return 0;
	139	{{/code}}
6.4	140
129.1	141	Here, values[2] corresponds to the "Nb days worked" column.
	142	[[image:Data_transform_creation_EN.png\|\|alt="Data transform creation"]]
6.4	143
6.8	144	(% start="5" %)
129.1	145	1. Click Apply.
	146	➡ You can view the result obtained in the column preview.
	147	1. Click the [[image:1738075980916-683.png\|\|alt="Fermer"]] button to finish.
	148	1. Click the header of Column 3 to rename it: enter Nb FTE.
	149	➡ The result is as follows:
	150	[[image:Data_transform_result_EN.png]]
6.4	151
129.1	152	You can now save:
6.5	153
129.1	154	* Click Finish and enter a name for the model: HR_Contracts.
6.11	155
129.1	156	== Data model HR_Complete{{id name="RH_Complet"/}} ==
6.11	157
129.1	158	We are now going to combine the data from the data models "Employee_Register" and "HR_Contracts" by performing a join.
	159	The join consists of aggregating the columns from several models thanks a column match called the join key.
	160	You can consult the page[[ Performing a data join>>doc:Digdash.user_guide.studio.Create_datamodel.Combine_data.extract_data_source_join.WebHome]] page for more details.
10.1	161
129.1	162	1. Click the New model button.
	163	1. In the Create a new data model box, select Join in the Other section.
	164	➡ The Join dialog box appears.
	165	1. Click the + button to the right of the Selected Data Sources section and select the data model Employee_Register.
	166	1. Repeat the operation to select the HR_Contracts data model.
	167	1. In the Key columns section, check the Employee ID column to use it as the join key.
	168	1. You can click Next to view the list of columns after the join.
	169	1. Click Finish and enter a name for the data model: HR_Complete.
8.2	170
129.1	171	== Data model Calendar ==
32.1	172
	173	(% class="box infomessage" %)
	174	(((
129.1	175	ℹ Only used for the first "Cartesian product" method.
32.1	176	)))
	177
129.1	178	We are now going to create the data model "Calendar" based on the file "Calendar_2020_2025_en.csv".
32.1	179
129.1	180	To do this:
32.1	181
129.1	182	1. Create a new model in the same way as before and add the file "Calendar_2020_2025_en.csv" to the document server.
	183	➡ The Excel File box is displayed with the data preview.
	184	1. In the Data selection section, select the First line as header checkbox.**
	185	[[image:Data_model_calendar_EN.png\|\|height="589" width="1116"]]**
	186	1. Click Finish and enter a name for the data model: Calendar.
32.1	187
11.1	188	(% style="line-height: 1.2; text-align: justify;" %)
129.1	189	= Application of the "Cartesian product" method =
11.1	190
	191	(% class="box warningmessage" %)
	192	(((
129.1	193	❗The Cartesian product is used here for a particular case, in a controlled setting and with limited data. If these prerequisites are not met performance and memory problems may arise.
11.1	194	)))
	195
	196	(% style="line-height:1.2; text-align:justify" %)
129.1	197	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.
11.1	198
12.3	199	(% style="line-height: 1.2; text-align: justify;" %)
129.1	200	== Step 2: Perform a join of the data models Calendar and HR_Complete data models ==
12.3	201
48.1	202	(% class="box infomessage" %)
	203	(((
129.1	204	ℹ This step follows on from [[Step 1: Create and configure the data models>>doc:\|\|anchor="Step1"]].
48.1	205	)))
	206
129.1	207	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.
34.1	208
	209	(% style="line-height: 1.2; text-align: justify;" %)
129.1	210	=== Modifying the model Calendar ===
34.1	211
129.1	212	1. Edit the data model "Calendar".
	213	1. Add an empty column.
	214	1. Open the Data Transform... tab.
	215	1. Click Add to create a new data transformation.
	216	➡ The interface Creating the transformation is displayed.
	217	1. Select //Column1 //from the Target column drop-down list.
	218	1. Enter the following code in the script editor:
70.1	219	{{code}}return 'a';{{/code}}
129.1	220	1. Click Apply and then the [[image:1738075980916-683.png\|\|alt="Fermer"]] button to finish.
	221	1. Click the column header then Rename to give the column the name Join.
	222	[[image:Calendar_a_EN.png\|\|alt="Join key"]]
	223	1. Click Finish to save.
11.1	224
129.1	225	=== Modify the model HR_Complete ===
12.2	226
129.1	227	We are going to modify the model HR_Complete model via the model HR_Contracts model.
12.6	228
129.1	229	1. Edit the data model HR_Contracts.
	230	1. Add a second column.
	231	1. Carry out the same operations as in the previous section to get the Join column.
	232	1. Click Finish to save.
	233	➡ A message warns you that the dependent models have been updated.
	234	[[image:Update_dependant_DM_FR.png\|\|alt="Warning" height="488" width="1147"]]
	235	1. Click OK.
	236	➡ In this way, the column is moved up to the model "HR_Complete":
	237	[[image:Join_in_HR_complete_EN.png\|\|alt="Join column"]]
12.4	238
129.1	239	=== Join the data models HR_Complete and Calendar ===
12.4	240
12.8	241	(% style="line-height:1.2; text-align:justify" %)
129.1	242	We are now going to combine the data models HR_Complete and Calendar by performing a join.
11.1	243
129.1	244	1. Create a new Join data model as described in the paragraph [[Data model HR_Complete>>doc:\|\|anchor="RH_Complet"]].
	245	1. Add the model HR_Complete model and then the model Calendar.
	246	1. Check the Join column in the Key columns section.
	247	[[image:Join_cartesian_product_En.png\|\|alt="Join"]]
12.4	248
129.1	249	== Step 3: Calculate the sum of FTEs ==
11.1	250
129.1	251	To continue, on the next screen we are going to create a calculated measure giving the sum of the FTEs:
11.1	252
129.1	253	1. Click Next to display the list of columns.
	254	1. Click the New measure button and then Calculated measure (advanced user).
	255	[[image:New_measure_EN.png\|\|alt="New measure" height="162" width="393"]]
	256	1. Enter the following code:
12.4	257
15.2	258	(% class="box warningmessage" %)
	259	(((
129.1	260	❗ Field references //(<Date>,// //<Start date>,// //<End date>// and //<Nb FTE>)// must be inserted via drag and drop or double-click from the Measurements/Dimensions panel for this to work in your environment.
15.2	261	)))
	262
129.1	263	{{code language="js"}}
	264	if (new Date(<Start date>1000) <= new Date(<Date>1000)
	265	&& (new Date(<End date>1000) >= new Date(<Date>1000)
	266	\|\| <End date>=='null'))
14.1	267	{
15.3	268	return <Nb ETP>;
14.1	269	}
	270	return 0;
	271	{{/code}}
	272
	273	(% start="4" %)
129.1	274	1. Enter the name of the measurement: Sum FTE.
	275	1. Uncheck the Compute after aggregation box.
	276	[[image:1752659215001-263.png\|\|alt="Sum FTE"]]
	277	1. Click OK.
	278	➡ The measure is added to the list of columns.
	279	1. Click Finish and name the model FTE Cartesian product.
14.1	280
15.3	281	(% style="line-height: 1.2; text-indent: 0.153543pt; text-align: justify;" %)
129.1	282	== Step 4: Create a table FTEs by department ==
11.1	283
129.1	284	We can now create a table displaying the sum of FTEs by department to visualize the result.
58.1	285
129.1	286	1. From the Flows tab, click the New flow button.
	287	1. In the Create a flow such as as chart or a {{glossaryReference glossaryId="Glossary" entryId="fabrique de documents"}}document builder{{/glossaryReference}} box, select Table.
	288	1. Select the data model FTE Cartesian product FTE.
	289	1. Drag and drop the dimension Department and then the measure Sum FTE.
	290	1. Filtering on the date 01/01/2023, you should obtain the following table:
	291	[[image:Table_FTE_dep_FR.png\|\|alt="Table"]]
19.1	292
11.1	293
129.1	294	= Application of the "data model "HR_Complete" alone" method =
19.2	295
129.1	296	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.
19.2	297
129.1	298	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".
19.2	299
129.1	300	== Step 2: Create the measures FTE N, FTE N-1 and FTE N & N-1 ==
37.1	301
49.1	302	(% class="box infomessage" %)
	303	(((
129.1	304	ℹ This step follows on from [[Step 1: Create and configure data models>>doc:\|\|anchor="Step1"]].
49.1	305	)))
	306
129.1	307	1. Edit the data model HR_Complete.
	308	1. Click Next to go to the Columns tab.
	309	1. Create a new calculated measure (advanced user).
19.2	310
	311	(% class="box warningmessage" %)
	312	(((
129.1	313	❗ Field references //(<Date>,// //<Start date>,// //<End date>,// //<Nb FTE>//...) must be inserted via drag and drop or double-click from the Measures/Dimensions panel for this to work in your environment.
19.2	314	)))
	315
129.1	316	=== Measure ETP N ===
19.2	317
129.1	318	To calculate the FTEs for year N :
37.1	319
129.1	320	1. Enter the following code:
19.2	321
129.1	322	{{code language="js"}}
19.2	323	var dateinit = new Date(new Date(Date.now()).getFullYear(),00,01);
	324
129.1	325	if(new Date(<Start date>*1000) <= new Date(Date.now()))
	326	if(<End date> == 'null' \|\| new Date(<End date>*1000) >= dateinit)
	327	return <Nb FTE>;
19.2	328	return 0;
	329	{{/code}}
	330
	331	(% start="2" %)
129.1	332	1. Enter the name of the measure: FTE N.
	333	1. Uncheck the box Compute after aggregation.
	334	[[image:Measure_FTE_N_EN.png\|\|alt="FTE N"]]
	335	1. Click OK.
19.3	336
129.1	337	=== Measure FTE N-1 ===
19.3	338
129.1	339	To calculate the FTEs for year N-1:
37.1	340
129.1	341	1. Enter the following code:
19.3	342
129.1	343	{{code language="js"}}
19.3	344	var dateinit = new Date(new Date(Date.now()).getFullYear()-1,00,01);
	345	var datefin = new Date(new Date(Date.now()).getFullYear()-1,11,31);
	346
129.1	347	if(new Date(<Start date>*1000) <= datefin)
	348	if(<End date> == 'null' \|\| new Date(<End date>*1000) >= dateinit)
	349	return <Nb FTE>;
19.3	350	return 0;
	351	{{/code}}
	352
	353	(% start="2" %)
129.1	354	1. Enter the name of the measure: FTE N-1.
	355	1. Uncheck the box Compute after aggregation.
	356	[[image:Measure_FTE_N-1_EN.png\|\|alt="FTE N-1"]]
	357	1. Click OK.
19.4	358
129.1	359	=== Measure FTE N & N-1 ===
20.1	360
129.1	361	To calculate the total FTE for year N and N-1 :
37.1	362
129.1	363	1. Enter the following code:
20.1	364
129.1	365	{{code language="js"}}
20.1	366	var dateinit = new Date(new Date(Date.now()).getFullYear()-1,00,01);
	367	var datefin = new Date(new Date(Date.now()).getFullYear(),11,31);
	368
129.1	369	if(new Date(<Start date>*1000) <= datefin)
	370	if(<End date> == 'null' \|\| new Date(<End date>*1000) >= dateinit)
	371	return <Nb FTE>;
20.1	372	return 0;
	373	{{/code}}
	374
	375	(% start="2" %)
129.1	376	1. Enter the name of the measure: FTE N & N-1.
	377	1. Uncheck the box Compute after aggregation.
	378	[[image:Measure_FTE_N&N-1_EN.png]]
	379	1. Click OK.
	380	1. Click Finish to save the changes.
20.2	381
129.1	382	== Step 3: Create measures gained, lost and stable FTE ==
37.1	383
20.4	384	(% style="line-height:1.2; text-align:justify" %)
129.1	385	From here, all that remains to do is to calculate the differences between these values to obtain the desired measures:
20.4	386
129.1	387	* Gained FTE represents new entrants in the current year
	388	* Lost FTE represents those leaving between the previous year and the current year
	389	* Stable FTE represents the others who have not moved
20.4	390
129.1	391	To do this, we will create 3 new measures calculated in the same way as above, with the following properties.
20.4	392
61.1	393	(% class="box warningmessage" %)
	394	(((
129.1	395	❗ Field references //(<ETP N>,// //<ETP N-1>//...) must be inserted via drag and drop or double-click from the Measures/Dimensions panel for this to work in your environment.
61.1	396	)))
	397
129.1	398	=== Measure Gained FTE ===
20.4	399
	400	1. (((
45.1	401	(% class="wikigeneratedid" id="HEntrezlecodesuivant:" %)
129.1	402	(% style="font-size:14px" %)Enter the following code:
20.4	403	)))
	404
129.1	405	{{code language="js"}}
	406	return <FTE N & N-1(sum)>-<FTE N-1(sum)>;
20.4	407	{{/code}}
	408
	409	(% start="2" %)
129.1	410	1. Enter the name of the measure: Gained FTE.
	411	1. Leave the Compute after aggregation box checked.
	412	1. Click OK.
20.4	413
129.1	414	=== Measure Lost FTE ===
20.4	415
	416	1. (((
45.1	417	(% class="wikigeneratedid" id="HEntrezlecodesuivant:-1" %)
129.1	418	(% style="font-size:14px" %)Enter the following code:
20.4	419	)))
	420
129.1	421	{{code language="js"}}
	422	return <FTE N & N-1(sum)>-<FTE N(sum)>;
20.4	423	{{/code}}
	424
	425	(% start="2" %)
129.1	426	1. Enter the name of the measure: Lost FTE.
	427	1. Leave the box Compute after aggregation checked.
	428	1. Click OK.
20.4	429
129.1	430	=== Measure Stable FTE ===
20.4	431
	432	1. (((
45.1	433	(% class="wikigeneratedid" id="HEntrezlecodesuivant:-2" %)
129.1	434	(% style="font-size:14px" %)Enter the following code:
20.4	435	)))
	436
129.1	437	{{code language="js"}}
	438	return <FTE N-1(sum)>-<Lost FTE(NO_AGG)>;
20.4	439	{{/code}}
	440
	441	(% start="2" %)
129.1	442	1. Enter the name of the measure: Stable FTE.
	443	1. Leave the Compute after aggregation box checked.
	444	1. Click OK.
20.4	445
129.1	446	You should obtain the following columns:
61.1	447
129.1	448	[[image:HR_Complete_columns_EN.png]]
61.1	449
129.1	450	Click Finish to save.
62.1	451
129.1	452	== Step 4: Check the result ==
20.3	453
129.1	454	To check the result, we are going to create a table showing the gained, lost and stable FTEs by department.
37.1	455
129.1	456	1. From the Flows tab, click on the New flow button and then select Table.
	457	1. Select the data model HR_Complete.
	458	1. Drag and drop the dimension Department and then the 3 measures Gained FTE, Lost FTE and Stable FTE.
	459	➡ 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.[[image:1752678432191-721.png\|\|alt="Table 2025"]]
	460	\\If we were in 2024, we would obtain the following result : no new employees but some left.
	461	[[image:1752678168842-178.png\|\|alt="Table 2024"]]
	462	1. Rename the {{glossaryReference glossaryId="Glossary" entryId="Flux"}}Flow{{/glossaryReference}} and click OK to save.
27.1	463
129.1	464	= Congratulations! =
27.1	465
129.1	466	You have successfully created an FTE tracker.
	467	Now all you have to do is apply it to your data!

Wiki source code of Calculer les Équivalents Temps Plein (ETP)

Sommaire