Count Distinct

Question

I need to create a report that has a distinct count (not just a count.) I have run into this problem many times and I have never found a good way to deal with it. So, I?m hoping there is some easy solution I?m missing. I?ll give my specific example for context.

My app records activities by resource specialists who provide assistance to patients. There is a patient table that is joined with an activities table (one patient can be associated with many activities.) The manager wants to assess the workload of her resource specialists. It is easy enough to count the activities by resource specialists or even the patient IDs linked to the activities, but the manager wants to answer the simple question about how many UNIQUE patients were helped.

I can group by the patient ID, but we are talking about very large numbers that bog down the report and makes it unusable. It also is not really the correct way to look at this. A calculated column that counts patients is the same as the counting the record. I need something like the SQL function ?COUNT (DISTINCT ... ?

In searching through this forum I found some people having similar issues and a reference to an underscore.js function HERE, but I?m looking for the most straightforward way to do this seemingly straightforward request. Thanks!

quickbasecoachd · Answer

I assume that we would need to specify a time period (say within the last 30 days).

During that specified time period, is the same Patient (PT) helped by Multiple Resource Specialists or can we assume that this does not happen.

If that is the case that a PT is assigned to one Resource Specialist, then we can ....

1. Go to the relationship where 1 PT has many Activities and make a Summary minimum of the Record ID of the Activity, subject to the filter that the [Activity Date] was on or after 30 days ago. Call it [Record ID# of 1st Activity in Analysis Period]

2. Look that up down to the Activity record.

3. Make a formula checkbox field [Activity was 1st Activity for this PT During Analysis Period?]

Then you would just make a summary count of the # of Activities for that Resourse Specials subject to the filter that

[Activity was 1st Activity for this PT During Analysis Period?] is checked.

tateforgey · Answer

Thanks for the response (and all your great responses, by the way!) I thought about doing some summary fields, but I'd like to be able to quickly answer questions about different time periods as well. (E.g., how many PTs last week, last month, or December of last year, in fiscal year 2017, etc.) Also, different resource specialists may actually help the same person.

I'm wondering if I should just say this can't be done in the activity reporting by resource specialist and seperate volume reports and patient reports. Then if I created some summary fields that wrote up the number of associated activities filtered on a certain month or year I could at least give the number of unique patients helped in those defined time frames from the patient table.

It seems a little puzzling there is no native ability to do a distinct count as a calculated column or something. The fact that you can see the number of groups in a report that groups by a field (like patient record ID in this case) makes it seem to me that the data is being calculated in QB somewhere. Oh well. Maybe in a future release.

hhersch · Answer

Just wanted to post back that we released&nbsp;Count Distinct as a feature in summary reports and are actively expanding it.

tateforgey · Answer

Yes!&nbsp; I saw that in the update&nbsp;notification&nbsp;and meant to update this post.&nbsp; Thanks for you continued and active development of this platform with very useful features like this.

scottervin · Answer

I need this as well but I can't seem to find the instructions on how to set up Count Distinct.&nbsp; Please give me some guidance.&nbsp; Thank you.

Forum Discussion

Count Distinct