Last working day vs variable Date field

[guillaume_gorli]

Dear All,

I am using a database coming from a time clock tool to determine how many employees there are in a compagny at a selected period.

Below are data coming out from the database

Date                          Date of working

Nom                          Name of the employee

SectionCode          Specific codification

Date entrée           Date when the employee started working in the compagny

Date sortie             Date when the employee left the compagny. Please note this field is never empty : either it is filed with the actual leaving date if the employee already left, either it is filed with '31/12/2050' as a fake leaving date if the employee is still working in the compagny

Total                          Hours worked

I need to determine on a monthly basis the number of employee per section code. The rule to apply is the following :

If an employee did not leave the compagny the last working day of the month, then is it counted as present. On the other hand if he leaves the compagny in 02/02/2021, it should not be counted in february.

I managed to get this right in a table first by setting up a new variable to get the last day of the month :

LastWorkDate(Date, NetWorkDays(Date, MonthEnd(Date))) as [Month end];

Then using IF formulas : « If([Date sortie]<>'31/12/2050' AND [Date sortie]>= [Month end] AND [Date entrée]>=[Month end],0,1).

For instance, in below table : If October 2020 is selected, Gregory is not counted in the employees as he will only be hired in 15/11/2020 and manon is counted in as she will only leave in December.

Meanwhile, when i try to sum this up in a table showing only SectionCode, i got lost summing up the lines. i shall get 4 as a results but i can't get it right.

Excel and qvw files attached for those who wants to help

Thanks in advance

Guillaume

[Martijn_W · Accepted solution]

Hi Guillaume,

So your objective is to do a proper headcount with the requirement that employees should only count if they work the full month, correct?

Considering that, in your model, you already keep a fact table with a date row for each employee/date combination, the easiest way and most efficient way is to use the script to evaluate each day. Look at the very tiny adjustment I made to your sample file.

[guillaume_gorli · Accepted solution]

Thanks a lot Martijin for your help. 

[Martijn_W]

You're very welcome!