Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with Qlik Sense formula - count if

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

20 Replies
Not applicable
Author

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?

d_prashanthredd
Creator III
Creator III

if(

    aggr(sum(if(Status = 'Passed', 1)), Name) = 0, Name

)

Not applicable
Author

This formula returns all names with 0 passed courses

d_prashanthredd
Creator III
Creator III

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

d_prashanthredd
Creator III
Creator III

What exactly you are looking for.. Name and count of subjects failed??

antoniotiman
Master III
Master III

Hi Rebecca,

tray this

Concat({<Name={"=Status='Failed'"}>} DISTINCT Name,'-')

or

Count({<Name={"=Status='Failed'"}>} DISTINCT Name)

Regards,

Antonio

Not applicable
Author

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 -

d_prashanthredd
Creator III
Creator III

Did you uncheck in measure or dimension. You have to for that expression.

Thanks.

danansell42
Creator III
Creator III

Just change the Passed to Failed in the expression

count({<[Course Completion Status]= {'Failed'}>} [Course Name])

sasiparupudi1
Master III
Master III

Hi Rebecca

Please try the formula

Count({<Status={'Failed'},Name={'=Count(distinct Status)=1'}>}Name)

hth

Sasi