Qlik Community

Qlik Sense App Development

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

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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

Re: Set analysis help (count based on a distinct modifier)

Try this

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

View solution in original post

6 Replies
Highlighted

Re: Set analysis help (count based on a distinct modifier)

Try this

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

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Set analysis help (count based on a distinct modifier)

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

Highlighted
Contributor III
Contributor III

Re: Set analysis help (count based on a distinct modifier)

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

Re: Set analysis help (count based on a distinct modifier)

You can use set analysis here:

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

Highlighted
Contributor III
Contributor III

Re: Set analysis help (count based on a distinct modifier)

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

Highlighted
Specialist
Specialist

Re: Set analysis help (count based on a distinct modifier)

Hi Ryan,

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

Regards,

Pankaj