Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
sarathi_pm
Contributor II
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

 

4 Replies
vvira1316
Specialist II
Specialist II

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

Sue_Macaluso
Community Manager
Community Manager

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
Sue_Macaluso
Community Manager
Community Manager

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
Gysbert_Wassenaar

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