Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
guillaume_gorli
Creator II
Creator II

Last working day vs variable Date field

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.

Capture.JPG

 

Excel and qvw files attached for those who wants to help

Thanks in advance

 

Guillaume

2 Solutions

Accepted Solutions
Martijn_W
Contributor III
Contributor III

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.

 

View solution in original post

guillaume_gorli
Creator II
Creator II
Author

Thanks a lot Martijin for your help. 

View solution in original post

3 Replies
Martijn_W
Contributor III
Contributor III

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
Creator II
Creator II
Author

Thanks a lot Martijin for your help. 

Martijn_W
Contributor III
Contributor III

You're very welcome!