Complex Count

[Germa]

Hi,

I have a table that looks something like below:

EmpID, LocationID,PositionID,StartDate,EndDate

1, A, 3, 1/23/2022, 3/5/2022

1, B, 3, 3/5/2022, -

2, A, 3, 7/14/2022, 12/01/2022

2, A, 1, 12/01/2022, 12/30/2022

3, A, 3, 4/17/2022, 6/03/2022

3, A, 3, 6/03/2022, -

I am trying to count distinct Employee IDs (EmpID) that have a valid EndDate in a given month, e.g.: 

• Employee 1 has a valid EndDate in March 2022 (as the Employee changes location), so the Employee should be counted in March 2022.
• Employee 2 has two valid end dates in December 2022 as the Employee changes positions on 12/01/2022 and then leaves the company on 12/30/2022. Employee 2 should be counted only once for December 2022.
• Employee 3 does NOT have a valid EndDate. The user was EndDated on 6/03/2022 but then re-started on the same day with the same Location and Position. If the Employee has an EndDate and a StartDate on the same day where neither the Position or the Location changes, then they should not be counted for that month. Employee 3 should NOT be counted in June 2022. 

Assume Employees can only hold one Position at a time and can be located at only one Location at at time.

Does anyone have ideas on how to approach this problem?

[marcus_sommer]

You need to resolve your start/end-dates to single dates and linking it to a master-calendar. A missing end-date could be replaced by today() or similar. For this you could use an IntervalMatch - Qlik Community - 1464547.