Calculating customer "status" based on multiple records in a table

[Joe_Mitchell]

The scenario: I have a “Customer” table. And I have a “Fees” table, where there is a record for every customer for each month that they paid for their subscription (so multiple records per customer). I want to “calculate” for each customer an indicator that tells me whether they have made each of their payments the last three months (Y/N). Any suggestions? Thanks

[Justin_Barrett]

That may be doable, but the “how” part depends on how you’re tracking the payment data. Specifically, how are you recording the month for each customer’s payment? Is it a date field, or something else?

[Joe_Mitchell]

I’m still creating things, so I can have the data be in any form that would serve the calculation. Likewise, I’m going to need to “calculate” confirmations for other events as well. The customers need to pay fees (monthly), and perform two different tasks each week. Each of these things will be tracked with separate records and will need an “OK” or “Completed” marker, often considering more than one month (or week) when doing the “calculation”.

Thanks for your time!

[Justin_Barrett]

For the payment tracking, here’s what I suggest. First, make a record on your [Fees] table for every customer every month, whether they paid or not. Use a link field to indicate which customer each record is for, and a {Paid} field where you record the date the customer paid, leaving it blank if they didn’t. Add a formula field (I named mine {Paid Y/N} ) with this formula:

IF(Paid, "Y", "N")

In your [Customers] table (guessing the name), add a rollup field (I named mine {Last 3} ) that rolls up the {Paid Y/N} field, using the following aggregation function:

RIGHT(ARRAYJOIN(values, ""), 3)

Finally, add another formula field called in [Customers] called something like {Current}:

IF({Last 3} = "YYY", "✅", "❌")

It may be possible to use other methods that don’t require a record for every customer every month, even if they didn’t pay, but it would be more complex. This (to me) felt like the simplest solution.

[Joe_Mitchell]

Thanks again for the time. Let me try this out tomorrow. I think the one wrinkle is…what if I have six months worth of records for a customer, but I only wanted the last three (or perhaps just the last one) to count in the calc of my indicator? But I see where you’re going with your example, and we’ll see what happens.

I have thought, too, about having history for all months, for a given customer, on the same record. I can see where this would potentially be easier to create the indicators I am looking for, but make for a LONG record that would continuously need to be added to. And if it were a weekly measure, that could get really long (though I know you can hide older columns).

I’ll keep working on it! Really excited and enjoying the journey!

[Justin_Barrett]

Joe_Mitchell:

what if I have six months worth of records for a customer, but I only wanted the last three (or perhaps just the last one) to count in the calc of my indicator?

That’s exactly how I built my example above, though perhaps it wasn’t as clear because I only put three months of payments in the sample list. No matter how many payment records you have for a given client, it still only collects the last three due to the RIGHT function in the rollup aggregator. If I change the 3 to a 1 in that function, it would only count the last record. How many you count is up to you, and can be different for each rollup you create to track different stats.