Forum Discussion

matakis's avatar
matakis
Qrew Member
2 months ago

Distinct Dates

I'm having trouble trying to build a report that only shows distinct dates for a given variable. Scenario: I have multiple entries with activity date ranges. If those multiple entries contain the same date(s), I only want to count each calendar date once, no matter how many entries there are that contain that date. Does anyone have advice on how to achieve this?

  • This can be done with Formula Queries as long as you don't have too much data in your table. Can you tell me how many records are in this table? 

    The issue is that when we use a Formula Query ends up having to compare every record to every other record in the table and the number of comparisons grows exponentially with the size of the table and at some point, Quickbase will simply say it won't let you build that formula because it would impact performance too much.

    The basic technique is to locate the Record ID, which has the lowest record ID number for all of its brothers and sisters who share dates in the same range. Then you filter the report to just run a report for those records. 

    And you also tell me whether you are checking this across every single record in the table, or are you checking this for a children of a parent record, such as appointment dates for a patient or something like that so you were looking for not brothers and sisters among all records, but brothers and sisters among records, which have the same parent. 

     

     

    • matakis's avatar
      matakis
      Qrew Member

      Thank you so much, Mark. Right now there are less than 100 records; I don't anticipate it will exceed 150 records per year moving forward (probably less than that). Each record has around 40 unique fields, but I am only concerned with two of them (the start and end date for each record).

      I should also note that around half of the fields are pulling in from a Dropbox pipeline (refreshing once per week, if that matters for performance purposes). There is a unique 5-digit record ID assigned to each record. 

      We want to validate the number of unique days across every single record. In effect, we are checking how many unique calendar days during which an activity occurred across all records. We want to determine the number of unique calendar days by the activity type for each record (separate field(s)), so we want to assess how many unique calendar days "Activity A" occurred and during how many days "Activity B" occurred across all records, with each calendar date being counted only once. I'm not sure which type of report would be ideal for this, perhaps a pie chart? Hopefully this addresses your last paragraph.

      Could you share more about how to build the Formula Query, assuming it's still appropriate based on what we're trying to achieve? Thanks again.

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        Can you tell me the names of your fields for the start and end dates and also the field for Activity Type.