Sum of Two Distinct Counts without Overlaps

[bamboopalm]

Hi, 

I have been struggling  to correctly display 3 KPIs based on the counts of distinct IDs for hours. 2 seem to be working, but I suspect the 3rd is not . 

I have a table with two columns, "Employee ID" which is a unique identifier, and "Activity Status", which can have two values "Active" or "Inactive"

1. The first KPI counts the number of "Active" employees based on the current selections of a filter pane. I believe this is  working correctly and am using the expression count(distinct {$<Activity Status = {'Active'}>}[Employee ID])

2. The second KPI is very similar to the first and counts the number of "Inactive" employees based on the current selections. Expression : count(distinct {$<Activity Status = {'Inactive'}>}[Employee ID])

3.The third (problematic) KPI needs to calculate the total number of unique employee IDs across both "Activity Status", but needs to account for the fact that if an employee goes from being "Active" to "Inactive" (or vice versa) they will appear in the data set twice. As it currently stands, I have count(distinct {$}[Employee ID] as my expression, but for some reason this number is always the same as the count of the first ("Inactive" count) KPI - which seems very unlikely to me. Basically, I am looking for the sum of the distinct counts from the expressions used in the  first and second KPIs if the value of the employee IDs are not the same. 

Any help would be greatly appreciated!

[Digvijay_Singh]

I think this should count those employees twice if they switched from Active to Inactive or vice versa - 

Count(distinct {<Activity Status = {'Active'}>}[Employee ID])

+

Count(distinct {<Activity Status = {'Inactive'}>}[Employee ID])

[Frank_S]

Check out the TOTAL qualifier.

This could be what you need in your 3rd KPI.

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/define-agg...

Kind regards...

[bamboopalm]

Thanks for the reply - this does not work and was one of the other things I've tried. It takes the two counts separately, so the employees in both groups will show up twice. 

[bamboopalm]

Thanks, this seems like a step in the right direction but I'm still not quite clear on how I would fit this into an expression. It seems like I need something along the lines of 

sum(Count(distinct {<Activity Status = {'Active'}>}[Employee ID])

+

Count(distinct {<Activity Status = {'Inactive'}>}[Employee ID])

if {<Activity Status = {'Active'}>}[Employee ID]) <> {<Activity Status = {'Inactive'}>}[Employee ID]))

but I know this syntax isnt quite right. I'm not sure where I would work in a total or aggr or something more elegant. 

[Manish_Kumar_]

Hey @bamboopalm 

May be you can try this

Count(distinct {<Activity Status = {'Active'}>*<Activity Status = {'Active'}>}[Employee ID])

OR

Count(distinct {<Activity Status = {'Active'}>+<Activity Status = {'Active'}>}[Employee ID])

[Digvijay_Singh · Accepted solution]

Check this out may be, as I understand you meant it should be either Active or Inactive and shouldn't be in both - 

Count(distinct {<Activity Status = {'Inactive'}>/<Activity Status = {'Active'}>}[Employee ID])

Count(distinct {<Activity Status /= {'Active','Inactive'}>}[Employee ID])

[bamboopalm]

Thank you very much!