Last working day vs variable Date field

guillaume_gorli — Fri, 12 Feb 2021 19:11:30 GMT

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

Re: Last working day vs variable Date field

Martijn_W — Fri, 12 Feb 2021 23:30:36 GMT

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.

Re: Last working day vs variable Date field

guillaume_gorli — Sat, 13 Feb 2021 00:07:30 GMT

Thanks a lot Martijin for your help.

Re: Last working day vs variable Date field

Martijn_W — Sat, 13 Feb 2021 00:38:55 GMT

You're very welcome!

topic Re: Last working day vs variable Date field in QlikView

Last working day vs variable Date field

Re: Last working day vs variable Date field

Re: Last working day vs variable Date field

Re: Last working day vs variable Date field