Count the number of unique values across 4 linked record fields

[Sara_Ophoff]

Hello - hoping someone can help! I feel (hope?) the solution is simple, but I am totally stumped!

GOAL: I am trying to summarize the total number of unique values across 4 linked record fields. 

CONTEXT: I run a coaching business that hosts group coaching sessions (called cohorts) for clients. These cohorts sessions can span across 2-3 months. I currently have a table that summarizes the total number of group sessions for each month / client. 

In addition to tallying up the total number of group sessions, I also want tally up the total number of unique cohorts that are meeting monthly. 

ATTEMPTED SOLUTION: I thought that maybe I could create two additional fields (1) that combines all the linked records for the month / client into a comma separated list and (2) a field that then counts the number of unique values within that string of text... but I can't get that to work. Maybe because its completely wrong! Should I be using a Roll-up field instead? I am totally stumped! 

VISUAL: Here is a visual of what I am trying to accomplish. The left represents the month / client (different clients are represented by different colors) and the 4 columns from the right are the linked records representing the cohorts (cohorts are represented by different colors) 

Any recommendations for what I need to do to create the "Count of Unique Linked Records" column? 

Thanks in advance for your help! 

[Jelani_Clay]

If I understand correctly, just make a count of each individual field using a rollup for Unique values. Then make another field that's the total of all of those.

[pressGO_design]

Methinks you have a data structure issue here…. but that’s just a guess from the screenshot. What are all of those RTW Session linked fields?

[Sara_Ophoff]

@Jelani_Clay I tried that at first, but the problem is, I need to know the # of unique values across ALL 4 fields. If I use a RollUp for Unique Values for each field and then add it up, I will get duplicates.

For example, with that method:

Row 1 would = 2 
Row 2 would = 2 
Row 3 would = 1
Row 4 would = 6
Row 5 would = 4 
Row 6 would = 5

So it wouldn't quite get the results I need! 

[Sara_Ophoff]

@pressGO_design oh no! I was afraid that might be the case (but really hope it's not!) - I just restructured the data to be set-up in this way so that I can count the total number of sessions by both the month and the client. 

The linked fields are group coaching cohorts (so think like Yellow = Cohort A, Orange = Cohort B, Green = Cohort C) 

This cohort info is being pulled from a table that is structured like this:

Cohort A

Company 1

Date of Session #1

Date of Session #2

Date of Session #3

Date of Session #4

And then each of those dates are linked to this table in this way:

• RTW: MON / YYYY - Company 

Which allows me to see the total number of cohort sessions for each month for each client (which was my original goal!)

Now I want to take it one step further and see how many unique cohorts I have running for each client during each month.

Is this possible? Let me know what you think if you need more info! Thanks in advance!

[pressGO_design]

If I were building this from scratch, I would start with 3 tables:

1. Companies - these are your clients
2. Cohorts - these are your groups. Each company can have more than one cohort; each cohort belong to only one company
3. Sessions - each record is the date and time of one session for one cohort.

There are fields in your Companies table that allow you to count the number of unique cohorts by month by Company, and then the aggregations on the bottom show you the total number of unique cohorts by month regardless of Company.

See base here.

[Sara_Ophoff]

@pressGO_design ohh interesting... I will play around with that and see if that helps!

The end goal I am trying to get to show all this data in ONE table that can be layered into an interface. So I am curious if there is anyway with how I have it currently set-up to pull out the number of unique cohorts across all 4 fields? 

@TheTimeSavingCo- any recommendations? You helped me structure the data in this way to see sessions by month (here!). But now I want to take it one step further.

Thank you everyone who has helped so far! 

[pressGO_design]

Ah - I didn't realize that you had an existing table and automations that were part of the picture.  I updated the demo base to reflect that and you can now see the number of unique cohorts in the Companies <> Months table. I didn't update the automations - I just added the records manually.

Honestly, I would avoid having multiple links to the same table. But that's just me.

[Sara_Ophoff]

@pressGO_design this is amazing! I am kind of embarrassed to ask... but how did you set this up? I am still kind of new to AT!

Currently, I am using count fields pointed at the linked records to sum up to that total number of sessions and then adding those up using a formula (Total sessions by month). I see that you have roll-ups instead of count fields for the sessions. 

Can you share:

• Why you used roll-up fields instead of the count fields to count the number RTW sessions? 
• How you used the roll-up for "Cohorts"
• What the formula is for Unique Cohorts? 

Looking forward to learning more! Thank you! 

[pressGO_design]

I think it helps that I’m not new to Airtable 😀😀😜😜

I used COUNTALL() for the rollups that count the number of RTW sessions, which is the same thing as using the COUNT field. Both of them count the number of linked records; one lives one its own and one lives with the rollups.

The rollup for "Cohorts" is a consequence of the way the data is set up. Because every session is one record and each session record is linked to one record in Companies <> Months, I can look up all of the Cohorts in all of the sessions, run an ARRAYUNIQUE(values) on them (that's the Cohorts field) and the I can use COUNTA(values) to count the number of values that are in that unique array.

But the data structure underpinning this is very different than the one you have. And I don't know that the data structure that you have is inherently incorrect - all I know is that the data structure you have is making it very difficult to answer the question that you posed. To find the number of unique cohorts using the data structure in your first screenshot, you would need to have multiple IF()s to check whether a field is blank and, if it's not blank, whether it's equal to another field that also needs to be checked for blankness, and then run through the values that are equal to check whether those values are equal to each other because the TRUE value for 2=3 is the same TRUE if 1=2  and therefore shouldn't be counted but a different TRUE if 1=4 and therefore should be counted. You can probably do this with a script, but that's not something I can help you with. Scripts and I have only the most nodding of acquaintances.