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

1 Solution

Accepted Solutions
d_prashanthredd
Creator III
Creator III

if(

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

)

View solution in original post

20 Replies
danansell42
Creator III
Creator III

You could use set analysis like the following:

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

devarasu07
Master II
Master II

Try like this,

count({<Status={'Passed'}>}DISTINCT Course)

d_prashanthredd
Creator III
Creator III

Hi Rebecca,

Based on the field you mentioned, below is the expression..

if(

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

)

Thanks,

Prashanth.

Not applicable
Author

I think this is working - do you think it's possible to exclude all people with 1 or more passed course, and only show Natalie with 0 passed courses?

d_prashanthredd
Creator III
Creator III

d_prashanthredd
Creator III
Creator III

agigliotti
Partner - Champion
Partner - Champion

maybe this:

sum( aggr( if( count( {< Status = {'Passed'} >} distinct Course ) >= 1, 1, 0 ), Course ) )

agigliotti
Partner - Champion
Partner - Champion

to exclude all people with 1 or more passed course (showing only Natalie) ,  below could be a calculated dimension:

aggr( if( count( {< Status = {'Passed'} >} distinct Course ) >= 1, null(), Name ), Name )

and remember also to untick "include null values" option.

Not applicable
Author

Thanks everyone for your input. I'm finding that the formulas provided are resulting in every name showing as either 1 or 0