topic Re: Set analysis help (count based on a distinct modifier) in App Development

Set analysis help (count based on a distinct modifier)

drminaker — Wed, 22 Dec 2021 15:42:54 GMT

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_ID	Course_ID
rminaker123	aabbcc
rminaker123	aabbcc
rminaker123	aabbcc
rminaker123	ddeeff
rminaker123	gghhii
rminaker123	gghhii
rminaker123	gghhii
rminaker123	jjjkkll
rminaker123	jjjkkll

Count(

{$<[Course_ID] ={ ?? }

[Student_ID]

)

Thanks in advance!

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

sunny_talwar — Thu, 20 Jul 2017 19:20:29 GMT

Try this

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

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

drminaker — Thu, 20 Jul 2017 19:23:05 GMT

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

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

drminaker — Thu, 20 Jul 2017 20:31:19 GMT

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,

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

sunny_talwar — Fri, 21 Jul 2017 03:17:24 GMT

You can use set analysis here:

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

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

drminaker — Fri, 21 Jul 2017 16:02:44 GMT

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

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

passionate — Fri, 21 Jul 2017 17:05:28 GMT

Hi Ryan,

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

Regards,

Pankaj