Forum Discussion

TateForgey's avatar
TateForgey
Qrew Assistant Captain
6 years ago

Count Distinct

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!

   
Formulas and functions
reports and charts

6 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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's avatar
    TateForgey
    Qrew Assistant Captain
    6 years ago
    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's avatar
    hhersch
    Qrew Captain
    6 years ago
    Just wanted to post back that we released Count Distinct as a feature in summary reports and are actively expanding it.
  • TateForgey's avatar
    TateForgey
    Qrew Assistant Captain
    6 years ago
    Yes! 

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