Inflated Count Distinct

tableaucommunity@tableau.com — Wed, 24 Jun 2015 22:46:19 GMT

I have some data that shows what program medical residents were enrolled in for each year of their residency training (see example data below).

I'd like to be able to create a pie chart that shows the distribution of first year residents across residency programs.To accomplish this, I use a Count Distinct on Student_ID and apply the filter year_of_residency = 1 to ensure that only records from the first year of residency training are captured.

However, what I'm really interested in is details of the earliest residency program that residents were enrolled in. If residents have switched residency program then they will have more than one record when year_of_residency = 1, e.g. StudentID 4 and 5. As you can see in the attached workbook, this issue makes the results look inflated. There are five students in the dataset, but seven appear in the pie chart.

To solve this problem, I'd like to be able to select the record that has a year_of_residency = 1 and the earliest residency_start_date for each studentID. I've read through other postings on count distinct and table calculations, but I can't figure out how to implement this in my workbook. If anyone could offer up some advice, I'd very much appreciate it.

Thanks!

studentID	year_of_residency	residency_program	residency_start_date
1	1	family medicine	1-Jul-04
1	2	family medicine	1-Jul-05
2	1	internal medicine	1-Jul-04
2	2	internal medicine	1-Jul-05
2	3	internal medicine	1-Jul-06
3	1	pediatrics	1-Jul-04
3	2	pediatric nephrology	1-Jul-05
4	1	family medicine	1-Jul-04
4	2	family medicine	1-Jul-05
4	1	pediatrics	1-Jul-07
4	2	pediatrics	1-Jul-08
5	1	internal medicine	1-Jul-04
5	1	psychiatry	1-Jul-05
5	2	psychiatry	1-Jul-06

Content in Tableau Community Forums

Inflated Count Distinct