CRM Interactions in last quarter or month

[Michael_William]

Using a Link and a Roll-up, I am easily able to get a “Last Contacted” field containing the most recent date of interaction with an account. Also, using a Link and a Roll-up, I am easily able to get a “Number of interactions” for an account, but what I’m looking for is to filter that “Number of interactions” by Month or Quarter. I want it to be really obvious and easy to see and report when looking at an account 1) when we last interacted with them, 2) how many interactions we’ve had in the last quarter.

Any advice or a view, field, formula, or look-up that might help me do this?

Thanks,
Mike

[W_Vann_Hall]

Caveat: I haven’t tested this, but I think it will work. If not, let me know, and I’ll correct.

Assumptions

1. You have an [Interactions] table containing one time-stamped record per interaction with the client.

2. You have a [Clients] table with a one-to-many link from client records to interaction records.

Steps

1. Define {Interactions::LastMonth}[1] as a formula field with the formula
   IF(IS_AFTER({DateOfInteraction},DATEADD(TODAY(),-30,'days')),1,BLANK())

2. Define {Interactions::LastQuarter} as a formula field with the formula
   IF(IS_AFTER({DateOfInteraction},DATEADD(TODAY(),-90,'days')),1,BLANK())

3. Define {Clients::LastMonth} as a rollup field rolling up {Interactions::LastMonth} with the aggregation formula of
   LEN(ARRAYJOIN(values)&',')/2

4. Define {Clients::LastQuarter} as a rollup field rolling up {Interactions::LastQuarter} with the aggregation formula of
   LEN(ARRAYJOIN(values)&',')/2

.__________

1. That is, define a field called {LastMonth} in the [Interactions] table.

[Michael_William]

Thanks!

That idea looks like it’ll work great and appears to be a rolling month and a rolling quarter (which is probably more helpful).

I’m also in the middle of testing an absolute month and quarter reference via an example in the ‘Sales CRM Dashboard’ shared project.

First they add a function field on the Interactions table to parse the month (DATETIME_FORMAT(Date,‘MM’))

I changed that to make quarters (IF(DATETIME_FORMAT(Date,‘MM’)<4,1,IF(DATETIME_FORMAT(Date,‘MM’)<7,2,IF(DATETIME_FORMAT(Date,‘MM’)<10,3,IF(DATETIME_FORMAT(Date,‘MM’)<13,4,“X”))))). I haven’t filtered year yet.

Then they use a separate (InteractionsCalc) table. With a lookup to the interactions table and a roll-up of the Months, they use (ARRAYJOIN(values,"|")) to make an array of the interactions date. Finally, they make a function field for each month to count up the values in the array ((LEN(InteractionMonths&"")-LEN(SUBSTITUTE(InteractionMonths&"",‘05’,"")))/2).

I think I like your idea more, but if anyone else sees this, just search for “Sales CRM Dashboard” in the shared projects to see an example of another implementation method.

Thanks again,
Mike

I’ll keep notes on my progress.

[Michael_William]

Just went with your idea, and it is much better than my original idea. The rolling quarter does a better job capturing my request.

One note, I ended up replacing your Step 4 aggregation formula with:
COUNTALL(values)
The Rollup field was pointed at my Interactions Link and the LastQuarter field.

At first glance it’s exactly what I was hoping to do.

Thanks again!

[W_Vann_Hall]

Thanks! The Sales CRM Dashboard is a base of mine, as well.[1] :winking_face:

The Dashboard code, although in need of a slight touch-up, tracks interactions by each month of the year. As you note, what I just provided gives you a rolling month or rolling quarter, which are probably more pertinent in a customer-facing app. If you want to track interactions in the current calendar month or current quarter, the following formulas should work:

Month:

IF(
    DATETIME_FORMAT(
        {DateOfInteraction},
        'M'
        )=DATETIME_FORMAT(
            TODAY(),
            'M'
            ),
    1,
    BLANK()
    )

Quarter:

IF(
    DATETIME_FORMAT(
        {DateOfInteraction},
        'Q'
        )=DATETIME_FORMAT(
            TODAY(),
            'Q'
            ),
    1,
    BLANK()
    )

. __________

1. That is, the Dashboard part is, which I grafted onto the standard Airtable Sales CRM template.