Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a data set showing courses, passed and failed, and I want to show in a table who has passed zero courses. An example of the data:
Region, Name, Course, Status
EMEA, John, A, Passed
EMEA, Natalie, A, Failed
EMEA, John, B, Passed
AMER, Frank, C, Passed
EMEA, Natalie, B, Failed
EMEA, John, C, Passed
AMER, Frank, C, Failed
I currently have the following formula which is not working (to show the number of courses passed or '0' if there are only failed courses):
Count (distinct if (([Course Completion Status] = 'Passed') >= 1, [Course Name], '0'))
Could somebody please help me with this? I'm sure it's quite simple but I'm very new to Qlik Sense!
Thanks,
Becs
Hi Devarasu, this is returning the correct number of passed courses, but I'd like to exclude all people with passed courses, so that only Natalie with 0 courses is showing. Is that possible?
if(
aggr(sum(if(Status = 'Passed', 1)), Name) = 0, Name
)
This formula returns all names with 0 passed courses
It returns "Natalie" only. You are looking for the same right?
Make sure that you unchecked Include null values.
Output:
If you uncheck Include null values, you will get
What exactly you are looking for.. Name and count of subjects failed??
Hi Rebecca,
tray this
Concat({<Name={"=Status='Failed'"}>} DISTINCT Name,'-')
or
Count({<Name={"=Status='Failed'"}>} DISTINCT Name)
Regards,
Antonio
I don't need to see # fails, only names of people with 0 passed courses. Your formula seems to return the name of the person with 0 passed courses, which is great, but even when i exclude null values i still see the rest of the names with a -
Did you uncheck in measure or dimension. You have to for that expression.
Thanks.
Just change the Passed to Failed in the expression
count({<[Course Completion Status]= {'Failed'}>} [Course Name])
Hi Rebecca
Please try the formula
Count({<Status={'Failed'},Name={'=Count(distinct Status)=1'}>}Name)
hth
Sasi