Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
sarathi_pm
New Contributor II

set analysis for possible combination counts

Hi,

I am looking for set analysis that can get me the count of students from the below table.

ID, Subject

 1, Maths

1, Science

2, Maths

2, Chemistry

3, Maths

Below are the 2 expressions I need some help.

1. I need to calculate the count of students who have taken only Maths - In this case the student ID 3 has taken only Maths. So the count I am looking for is only 1.

2. I need to calculate the count of students who have taken Maths and Science - In this case, the student 1 has taken both Maths and Science. So the count I am looking for is only 1.

Any help is much appreciated.

Thanks

Sarathi

 

Tags (1)
4 Replies
vvira1316
Valued Contributor II

Re: set analysis for possible combination counts

Script

NoConcatenate
[StudentCourseData]:
LOAD * Inline [
ID, Subject
1, Maths
1, Science
2, Maths
2, Chemistry
3, Maths
];

NoConcatenate
[NumOfCoursesByStudent]:
LOAD
ID,
Count(DISTINCT Subject) as NumOfCourses
Resident [StudentCourseData]
Group By ID;

 

Expressions

='Only Math' & chr(10) & Count({<NumOfCourses={1}, Subject={'Maths'}>} DISTINCT ID)

='Math & Science' & chr(10) & Count({<NumOfCourses={2}, Subject={'Maths'}, Subject={'Science'}>} DISTINCT ID)

='Math & Science' & chr(10) & 'Student ID' & chr(10) & Max({<NumOfCourses={2}, Subject={'Maths'}, Subject={'Science'}>} DISTINCT ID)

 

In table

 

If(NumOfCourses = 1 and Subject = 'Maths', 'Only Math Yes', 'Only Math No')

image.PNG

Community Manager
Community Manager

Re: set analysis for possible combination counts

Can you tell me which product this is for I'd like to move it to the correct forum. Thank you! @sarathi_pm @vvira1316

Sue Macaluso
Community Manager
Community Manager

Re: set analysis for possible combination counts

Can you tell me which product this is for I'd like to move it to the correct forum. Thank you! @sarathi_pm @vvira1316

Sue Macaluso
MVP & Luminary
MVP & Luminary

Re: set analysis for possible combination counts

Only Maths: count({<Subject={'Maths'},ID={"=count(distinct Subject)=1"}>}distinct ID)
Maths and Science: count({<Subject={'Maths'}>*<ID=P({<Subject={'Science'}>}ID)>} distinct ID)


talk is cheap, supply exceeds demand