Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
drminaker
Contributor III
Contributor III

Set analysis help (count based on a distinct modifier)

Hi,

I need some help with set analysis please.

I'm trying to count the number of student records (Student ID), but I only want to count each student record once per course (Course ID).

For example, with this data I'd like to get the count of 4 (rminaker123 has attended 4 courses).

  

Student_IDCourse_ID
rminaker123aabbcc
rminaker123aabbcc
rminaker123aabbcc
rminaker123ddeeff
rminaker123gghhii
rminaker123gghhii
rminaker123gghhii
rminaker123jjjkkll
rminaker123jjjkkll

Count(

{$<[Course_ID] ={ ?? } 

>}

[Student_ID]

)

Thanks in advance!

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Sum(Aggr(Count(DISTINCT Student_ID), Course_ID))

View solution in original post

6 Replies
sunny_talwar

Try this

Sum(Aggr(Count(DISTINCT Student_ID), Course_ID))

drminaker
Contributor III
Contributor III
Author

Thank you Sunny (as always)! Of course, I was over complicating.

drminaker
Contributor III
Contributor III
Author

Hi (again) Sunny,

Just curious, is there a way to write this using set analysis --  in case I wanted to apply more filters to it (e.g., possibly a status or a date)?  Or would the best practice be to use variables and then use the variable in the set analysis or formula?

Thanks,

R.

sunny_talwar

You can use set analysis here:

Sum({<SetAnalysisHere>}Aggr(Count({<SetAnalysisHere>}DISTINCT Student_ID), Course_ID))

drminaker
Contributor III
Contributor III
Author

Thanks again. I didn't realize I could do that. I shall give it a try.

passionate
Specialist
Specialist

Hi Ryan,

You can even try Group by in load script if your size is huge.

Regards,

Pankaj