Modifying an existing app for unusual reporting needs

Yes, that is the right structure.  I think that the identification of a Case Interaction being the first one for a Quarter can no doubt be done with some crazy Formula Query, but let's first try to muddle our way through  with regular relationships.

The Interactions table could use some helper fields so they know the Quarter Identifications for the Current Year.  We will also want to know the Quarters for the Previous year, because no doubt in January you need to report on how many New clients you saw last year.

So let's make some fields which will result in a format like 2025-Q1

[Current Year-Q1]

formula is:

var text YYYY = ToText(Year (Today());

var text Q = "Q1"

List("-", $YYYY, $Q)

Then make the other 3 Quarters.

Then repeat for last year

[Last Year Q1]

formula is:

var text YYYY = ToText(Year (Today()-1);

var text Q = "Q1"

List("-", $YYYY, $Q)

Then make a field called [Interaction Year-Q]

var text YYYY = ToText(Year ([Interaction Date]));

var text Q = IF(

Month([Interaction Date]) < =3, "Q1",

Month([Interaction Date]) < =6, "Q2",

Month([Interaction Date]) < =9, "Q3",

Month([Interaction Date]) < =12, "Q4")

List("-", $YYYY, $Q)

Here are your main relationships: 

Clients < Cases < Interactions

One Client has many Cases which have Many Interactions

on this relationship

Cases < Interactions

Make a Summary field for [Interaction was in Current Q1?]

Any record where

[Interaction Year-Q] is equal to the value in the field [Current Year-Q1]

Set the field properties to show totals even though it is a check box field.

Do some kind of sanity check on that field and once you believe it works, then duplicate that field for any other quarters you want to track, like maybe 7 more copies fields for each Quarter this current Year and for last year.

Then on the this relationship

Clients < Cases

Summarize those 8 fields again up to Clients with the "Any" but no filter and that will identity Clients which have had Interactions in, respectively, those 8 Quarters.  So 8 more Summary fields set to show totals.

You could then, for example, make a simple table report of all Clients who had Interactions in Any of those 8 time periods showing the 8 Any Interaction fields across the columns and then totals at the bottom.  You would make normal extra fields on the Client record to calculate if they had any service in the Current Year and Last Year, respectively.

like

    [Any Client Interactions Current Year Q1?]

or [Any Client Interactions Current Year Q2?]

or [Any Client Interactions Current Year Q3?]

or [Any Client Interactions Current Year Q4?] 

So that setup will give you a report showing the answer to one of your questions:

How many total Clients were served (each Quarter)

and also the formula field will give you # of unique clients served in the year.

I will post back separately with the question about now many New clients were served each Quarter, but I think it's trivial once you have those 8 summary fields built on the Client record.