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?
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.
