Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So given
EmployeeID, Month, Flag1, Flag2
1001, 1, 1, 0
1001, 1, 0, 1
1001, 1, 0, 0
1002, 1, 1, 0
1002, 1, 0, 0
1003, 1, 0, 1
1003, 1, 0, 0
1003, 1, 0, 0
I would like a count of the employees that have a 1 in both Flag1 and Flag2 despite it being on different rows.
Finding the count of employeeID's with a flag1 of 1 is easy using count({<Flag1 = {1}>} EmployeeID) which gives me 2
Of course using count({<Flag1 = {1}, Flag2 = {1}>} EmployeeID) doesn't work because such a row doesn't exist.
I came up with
aggr(count({<Flag1 = {1}>} EmployeeID) + count({<Flag1 = {2}>} EmployeeID), EmployeeID)
Or
count(DISTINCT aggr(count({<Flag1 = {1}>} EmployeeID) + count({<Flag1 = {2}>} EmployeeID), EmployeeID))
How's that look, would you do something different?
You may also try the follwoing expression,
Sum( Aggr (IF(Sum({1}Flag1)>0 and Sum({1}Flag2)>0, 1, 0), EmployeeID))
You may also try the follwoing expression,
Sum( Aggr (IF(Sum({1}Flag1)>0 and Sum({1}Flag2)>0, 1, 0), EmployeeID))
Yes! That looks much better!