Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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

Highlighted
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
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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