Last modified by Aurelie Bertrand on 2025/07/17 09:36
Show last authors
1 (% class="box infomessage" %)
2 (((
3 🙋 This tutorial is intended for **advanced users**.
4
5 ⏱ It is designed to be followed independently in **2 hours**.
6 )))
7
8 ----
9
10 {{ddtoc/}}
11
12 ----
13
14 (% style="line-height: 1.2; text-align: justify;" %)
15 = Introduction =
16
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.
19
20 To do this, we will use several fictitious data sets:
21
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.
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;" %)
27 = Prerequisites =
28
29 To complete this tutorial, you will need to :
30
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]].
34
35 (% class="box" %)
36 (((
37 ℹ The screenshots in this tutorial were produced using the Chrome browser. There may be slight differences depending on the browser.
38 )))
39
40 (% style="line-height: 1.2; text-align: justify;" %)
41 = Overview of methods =
42
43 There are two ways of dealing with this subject, each with its own advantages and disadvantages:
44
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.
47
48 (% style="line-height: 1.2; text-align: justify;" %)
49 = Step 1: Create and configure data models{{id name="Step1"/}} =
50
51 Before you can work with the methods described above, you need to integrate the data into Digdash.
52
53 (% class="box infomessage" %)
54 (((
55 ℹ This step is common to both methods.
56 )))
57
58 == Model Employee_Register ==
59
60 === Import the data "Employee_Register" data ===
61
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.
63
64 To do this:
65
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.
78
79 (% class="box infomessage" %)
80 (((
81 ℹ If the //**UserDocs **//document server is selected the documents are only accessible to the user who uploaded them.
82 )))
83
84 (% start="10" %)
85 1. In the **Search remote files** box, select **"Employee_Register"**.
86 1. Click **OK.**
87
88 The **Excel File **window appears. It offers data selection options and a preview of the data.
89
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:
91
92 * In the **Data selection** section, select the **First row as header** checkbox.
93 [[image:Preview_register_EN.png||height="622" width="1253"]]
94
95 (% class="wikigeneratedid" %)
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.
97
98 === Configure the data model ===
99
100 The data model configuration window opens on the **Columns** tab.
101
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:
104
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)**.
108
109 You can then save:
110
111 * Click **Finish **and enter a name for the model: **Employee_Register**.
112
113 == Model HR_Contracts ==
114
115 === Import data "HR_Contracts" ===
116
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.
118
119 To do this:
120
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 **.**
124
125 === Calculate the FTE ===
126
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:
128
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:
134
135 {{code}}
136 if(values[2])
137 return values[2]/5;
138 return 0;
139 {{/code}}
140
141 Here, values[2] corresponds to the "Nb days worked" column.
142 [[image:Data_transform_creation_EN.png||alt="Data transform creation"]]
143
144 (% start="5" %)
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]]
151
152 You can now save:
153
154 * Click **Finish **and enter a name for the model: **HR_Contracts**.
155
156 == Data model HR_Complete{{id name="RH_Complet"/}} ==
157
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.
161
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**.
170
171 == Data model Calendar ==
172
173 (% class="box infomessage" %)
174 (((
175 ℹ Only used for the first "Cartesian product" method.
176 )))
177
178 We are now going to create the data model "Calendar" based on the file  "Calendar_2020_2025_en.csv".
179
180 To do this:
181
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**.
187
188 (% style="line-height: 1.2; text-align: justify;" %)
189 = Application of the "Cartesian product" method =
190
191 (% class="box warningmessage" %)
192 (((
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.**
194 )))
195
196 (% style="line-height:1.2; text-align:justify" %)
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.
198
199 (% style="line-height: 1.2; text-align: justify;" %)
200 == Step 2: Perform a join of the data models Calendar and HR_Complete data models ==
201
202 (% class="box infomessage" %)
203 (((
204 ℹ This step follows on from [[Step 1: Create and configure the data models>>doc:||anchor="Step1"]].
205 )))
206
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.
208
209 (% style="line-height: 1.2; text-align: justify;" %)
210 === Modifying the model Calendar ===
211
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:
219 {{code}}return 'a';{{/code}}
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.
224
225 === Modify the model HR_Complete ===
226
227 We are going to modify the model HR_Complete model via the model HR_Contracts model.
228
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"]]
238
239 === Join the data models HR_Complete and Calendar ===
240
241 (% style="line-height:1.2; text-align:justify" %)
242 We are now going to combine the data models HR_Complete and Calendar by performing a join.
243
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"]]
248
249 == Step 3: Calculate the sum of FTEs ==
250
251 To continue, on the next screen we are going to create a calculated measure giving the sum of the FTEs:
252
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:
257
258 (% class="box warningmessage" %)
259 (((
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.
261 )))
262
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'))
267 {
268 return <Nb ETP>;
269 }
270 return 0;
271 {{/code}}
272
273 (% start="4" %)
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**.
280
281 (% style="line-height: 1.2; text-indent: 0.153543pt; text-align: justify;" %)
282 == Step 4: Create a table FTEs by department ==
283
284 We can now create a table displaying the sum of FTEs by department to visualize the result.
285
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"]]
292
293
294 = Application of the "data model "HR_Complete" alone" method =
295
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.
297
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".
299
300 == Step 2: Create the measures FTE N, FTE N-1 and FTE N & N-1 ==
301
302 (% class="box infomessage" %)
303 (((
304 ℹ This step follows on from [[Step 1: Create and configure data models>>doc:||anchor="Step1"]].
305 )))
306
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).
310
311 (% class="box warningmessage" %)
312 (((
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.
314 )))
315
316 === Measure ETP N ===
317
318 To calculate the FTEs for year N :
319
320 1. Enter the following code:
321
322 {{code language="js"}}
323 var dateinit = new Date(new Date(Date.now()).getFullYear(),00,01);
324
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>;
328 return 0;
329 {{/code}}
330
331 (% start="2" %)
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**.
336
337 === Measure FTE N-1 ===
338
339 To calculate the FTEs for year N-1:
340
341 1. Enter the following code:
342
343 {{code language="js"}}
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
347 if(new Date(<Start date>*1000) <= datefin)
348 if(<End date> == 'null' || new Date(<End date>*1000) >= dateinit)
349 return <Nb FTE>;
350 return 0;
351 {{/code}}
352
353 (% start="2" %)
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**.
358
359 === Measure FTE N & N-1 ===
360
361 To calculate the total FTE for year N and N-1 :
362
363 1. Enter the following code:
364
365 {{code language="js"}}
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
369 if(new Date(<Start date>*1000) <= datefin)
370 if(<End date> == 'null' || new Date(<End date>*1000) >= dateinit)
371 return <Nb FTE>;
372 return 0;
373 {{/code}}
374
375 (% start="2" %)
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.
381
382 == Step 3: Create measures gained, lost and stable FTE ==
383
384 (% style="line-height:1.2; text-align:justify" %)
385 From here, all that remains to do is to calculate the differences between these values to obtain the desired measures:
386
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
390
391 To do this, we will create 3 new measures calculated in the same way as above, with the following properties.
392
393 (% class="box warningmessage" %)
394 (((
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.
396 )))
397
398 === Measure Gained FTE ===
399
400 1. (((
401 (% class="wikigeneratedid" id="HEntrezlecodesuivant:" %)
402 (% style="font-size:14px" %)Enter the following code:
403 )))
404
405 {{code language="js"}}
406 return <FTE N & N-1(sum)>-<FTE N-1(sum)>;
407 {{/code}}
408
409 (% start="2" %)
410 1. Enter the name of the measure: **Gained FTE**.
411 1. Leave the **Compute after aggregation** box checked.
412 1. Click **OK**.
413
414 === Measure Lost FTE ===
415
416 1. (((
417 (% class="wikigeneratedid" id="HEntrezlecodesuivant:-1" %)
418 (% style="font-size:14px" %)Enter the following code:
419 )))
420
421 {{code language="js"}}
422 return <FTE N & N-1(sum)>-<FTE N(sum)>;
423 {{/code}}
424
425 (% start="2" %)
426 1. Enter the name of the measure: **Lost** **FTE**.
427 1. Leave the box **Compute after aggregation** checked.
428 1. Click **OK**.
429
430 === Measure Stable FTE ===
431
432 1. (((
433 (% class="wikigeneratedid" id="HEntrezlecodesuivant:-2" %)
434 (% style="font-size:14px" %)Enter the following code:
435 )))
436
437 {{code language="js"}}
438 return <FTE N-1(sum)>-<Lost FTE(NO_AGG)>;
439 {{/code}}
440
441 (% start="2" %)
442 1. Enter the name of the measure: **Stable FTE**.
443 1. Leave the **Compute after aggregation** box checked.
444 1. Click **OK**.
445
446 You should obtain the following columns:
447
448 [[image:HR_Complete_columns_EN.png]]
449
450 Click **Finish** to save.
451
452 == Step 4: Check the result ==
453
454 To check the result, we are going to create a table showing the gained, lost and stable FTEs by department.
455
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.
463
464 = Congratulations! =
465
466 You have successfully created an FTE tracker.
467 Now all you have to do is apply it to your data!