Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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
Perhaps this?
Count(DISTINCT {$< UserId = {"=[Course Progress] > 80 and Interval([Course Learning Time],'m') > Interval(([Course Expected Time]/2),'m')>} UserID)
no need for Interval the letter m is part of the column name
and the values are numbers
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)
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