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: 
did
Employee
Employee

HR use case: IntervalMatch & nested aggregations

Hi folks,

 

I have a typical HR use case with employee’ job contract times (employed from till)  and missing times (from till).

Thanks to intervalmatch recommendations of Henric Cronström (HIC) it was possible to solve the 1st main requirement (calculate and show only those employees which did not miss in selected month).

Now the 2nd main requirement is: taking care of weekends and public holidays to calculate how many days per month each employee

  • must work
  • actually worked
  • actually missed.

What I managed so far:

  • How many days per month an employee must work
    • Solution: all NetWorkDays of selected month – Public Holidays
  • If employee did not miss in selected month (i.e. he/she missed somewhere in the past or never missed at all)
    • Solution: all NetWorkDays of selected month – Public Holidays
    • Example_A (please see attached pdf)
      • in this case missing days are null, which is fine
 

Currently my difficulty is:

  • If employee has only 1 missing period in selected month
    • Example_B (please see attached pdf)
      • in this case missing days = 3 (because 4.01.2020 was Saturday).
      • Works fine if
        • ONLY one row with missing times per employee is available in selected month
        • And missing time period ends within the 1st week
  • If employee has more than 1 missing period in selected month (i.e. there are several rows with missing times per employee in selected month)
    • Example_C (please see attached pdf)
       

       

    • Example_D (please see attached pdf)
       

       

Any help is more than appreciated.

 

Denis

0 Replies