Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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!

1 Solution

Accepted Solutions
Highlighted

Try this

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

View solution in original post

6 Replies
Highlighted

Try this

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

View solution in original post

Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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.

Highlighted

You can use set analysis here:

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Specialist
Specialist

Hi Ryan,

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

Regards,

Pankaj