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
You could use set analysis like the following:
count({<[Course Completion Status]= {'Passed'}>} [Course Name])
Try like this,
count({<Status={'Passed'}>}DISTINCT Course)
Hi Rebecca,
Based on the field you mentioned, below is the expression..
if(
aggr(sum(if(Status = 'Passed', 1)), Name) = 0, Name
)
Thanks,
Prashanth.
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?
maybe this:
sum( aggr( if( count( {< Status = {'Passed'} >} distinct Course ) >= 1, 1, 0 ), Course ) )
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.
Thanks everyone for your input. I'm finding that the formulas provided are resulting in every name showing as either 1 or 0