20 Replies Latest reply: Aug 10, 2017 8:17 AM by Rebecca Parkinson

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

• Re: Help with Qlik Sense formula - count if

You could use set analysis like the following:

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

• Re: Help with Qlik Sense formula - count if

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?

• Re: Help with Qlik Sense formula - count if

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.

• Re: Help with Qlik Sense formula - count if

Just change the Passed to Failed in the expression

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

• Re: Help with Qlik Sense formula - count if

Hi Rebecca

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

hth

Sasi

• Re: Help with Qlik Sense formula - count if

Try like this,

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

• Re: Help with Qlik Sense formula - count if

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?

• Re: Help with Qlik Sense formula - count if

Hi Rebecca,

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

if(

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

)

Thanks,

Prashanth.

• Re: Help with Qlik Sense formula - count if

maybe this:

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

• Re: Help with Qlik Sense formula - count if

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

• Re: Help with Qlik Sense formula - count if

if(

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

)

• Re: Help with Qlik Sense formula - count if

This formula returns all names with 0 passed courses

• Re: Help with Qlik Sense formula - count if

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

• Re: Help with Qlik Sense formula - count if

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

• Re: Help with Qlik Sense formula - count if

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 -

• Re: Help with Qlik Sense formula - count if

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

Thanks.

• Re: Help with Qlik Sense formula - count if

Got it - it#s working now! Thanks so much!

• Re: Help with Qlik Sense formula - count if

Hi Rebecca,

tray this

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

or

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

Regards,

Antonio