Forum Discussion

ChristineK's avatar
ChristineK
Qrew Assistant Captain
5 hours ago

Help me write a formula that is dynamic

Me again.

This is the perfectionist in me. I'd love suggestions on if it's possible (and how) to write a formula that calculates a person's age based upon DOB field, converts it into a category ("0-12", "13-17", etc.), AND ALSO where that field can apply the reference date based upon a report filter.

So, if in the future I need to see a report and the age needs to convert to the date where it would be in the beginning of that date range (or even more ideally, if it can reference the first date of service within the filtered time range on a connected table and apply the rule as it would have been in that time frame), that way I can always see what we would have received as a report-out at that point in time if I needed to go back and spot-check something.

I know that sounds terribly convoluted. It's for grants reporting, and honestly these are some of the items that get at some of the most obnoxious calculations we're asked to report out on...... If I can't make it perfect, no big deal, but I thought I'd see what you all can conjure up.

Example: Person is 17 at the time they come to receive services for the first time. The dynamic field should be able to reference my "Services" table, see when the first Service record within the filtered quarter is, and apply the age range. The following quarter, we'd actually report them out again, but applied to the new quarter -- and let's say they turned 18, so for THAT filter, I want it to now produce the updated age group.

Ultimately, accuracy within my present reporting period is of chief importance, but ideally, the ability to pull up historic records and check would really be fantastic.

formulas

4 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 hours ago

     It sounds like you have a relationship where one person has many "Service Provided records". 

    You can make a new field there for Age at Service Provided date.

    var date DOB = [My Date of Service Provided field];  // update with your own field here

    Year(Today())-Year($DOB)
    -
    If(
    Month(Today())<Month($DOB)
    or
    (Month(Today())=Month($DOB) and Day(Today())<Day($DOB)),1,0)

     Then you can make a [Age Category at Date of Service] field there too. 

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 hours ago

    Once that tests OK then make a new field for [Age Category]

    IF(

    IsNull([My Date of Birth field]), "",

    [Age] <=12, "0-12",

     [Age] <=17, "13-17",

    [Age] <=25, "18-25",

    etc

    [Age] <= 65, "50-65",

    , "66+")

     

     

  • ChristineK's avatar
    ChristineK
    Qrew Assistant Captain
    4 hours ago

    Side note: Qrew forum request -- the ability to edit posts haha. I'm going to add on some additional things and make this already-dense request a little more so! :-D 

    Here is one other related thing I'm trying to do -- which is not directly related to the above, but does affect it, so it's worth mentioning.

    For grant reporting, I also need to be able to capture "New This Year", and "New This Quarter". Just to make things EXTRA fun, everyone is considered "New" at the beginning of the FY, even when they're legacy clients......

    I see in a competitor database that was developed specifically for these kinds of grants, there are tick boxes for "New this quarter" and "New this year" right in a master Client table -- but without being able to see how those are behaving, I'm curious how I might build something like that within my QB app. Maybe, it's a report on a dashboard that somehow counts "Clients with any Service records this quarter, where the earliest service record since the last fiscal year falls within this quarter"?  These are the reporting requirements that turn my brain inside out. 🙃🤯

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      3 hours ago

      I will a respond step wise. To create a numeric field hat calculates a person's age, you can use this formula.

      var date DOB = [My Date of Birth field];  // update with your own field here

      Year(Today())-Year($DOB)
      -
      If(
      Month(Today())<Month($DOB)
      or
      (Month(Today())=Month($DOB) and Day(Today())<Day($DOB)),1,0)