count of employees where all related records satisfy a condition

[ali_hijazi]

Hello got the data above
I want to count the users who got course progress >80 on all the courses and the course learning time > course expected duration / 2 for all courses per employee
so if an employee got a record for a course that doesn't satisfy the above conditions, I don't want to count him
even if one other course satisfies the above condition

I wrote at first the following expression:
count({<UserId = {"=[Course Progress] > 80"}UserId) and the this returned 4 which is correct
then I wrote
count({<UserId = {"=[Course Progress] > 80 and [Course Learning Time] > [Course Expected Time]/2"}UserId) and this returned 0 while it should return 1 because the employee Sebastien Garon satisfies these 2 conditions

kindly advise

[njmaehler]

To avoid complex set analysis to maintain later, is there the opportunity for you to flag the records that meet the [Course Learning Time] > [Course Expected Time]/2 criteria because it will be easier in the script than trying to sum the values in the set analysis?
The suggestion would be:
when loading your table above add another line to say 
if ([Course Learning Time] > [Course Expected Time]/2, 1, 0) as FlagValidTime

then your set analysis to count the users would simply be 
count({$< [Course Progress] = {">=80"}, FlagValidTime = {1}>} UserID)
You could even provide and input box for the 80 mark and then substitute a variable into the set analysis instead of hard coding the 80. 
Something like: 
count({$< [Course Progress] = {">=$(vInputValue)"}, FlagValidTime = {1}>} UserID)

[ali_hijazi]

Your suggestion may simplify set analysis but what I want to count is the employees who got progress >80 for all courses your suggestion will count employees that have one row with progress >80

[BrunPierre]

Perhaps this?

Count(DISTINCT {$< UserId = {"=[Course Progress] > 80 and  Interval([Course Learning Time],'m') > Interval(([Course Expected Time]/2),'m')>} UserID)

[ali_hijazi]

no need for Interval the letter m is part of the column name
and the values are numbers

[BrunPierre]

Could it be null in the [Course Expected Time]?

Count({$ -< [Course Expected Time]={'*'},UserID = {"=[Course Progress] > 80 and [Course Learning Time] > [Course Expected Time]/2 "}>}UserID)

[ali_hijazi · Accepted solution]

it turned out that I cannot count a field and use that same field to fomulate a condition as per below:

count({<UserId={"=[Course Progress] >80 and [Course Learning Time] > [Expected Time] / 2 "}>} UserId)
but if I create a copy of the field UserId (UserId as Field1) then

count({<Field1={"=[Course Progress] >80 and [Course Learning Time] > [Expected Time] / 2 "}>} UserId) works just fine