How to count unique values in an object's records?

I have a Knack object that records interactions with our clients. Each interaction is connected to a client record in a different object. Some clients only have 1 interaction with us, but others have many interactions over time.

I would like Knack to provide me with the information on both how many total interactions we had over a certain time period, but also how many unique clients we interacted with over that same time period. It’s easy enough to create a report that shows the total interactions, but I can’t figure out a way to count just how many unique clients we had during that same time period.

If I were doing this in Excel I’d use a COUNTIF statement or maybe just make a pivot table and simply count the rows.

Any suggestions on implementation in Knack?

Hello Rick,

Create a count column in the Client object (which counts the total interactions) within a certain period(30 days/year or something else) if there is a date field in the interactions oject. And also you can create another object which connects to all client records by default. So in that new object create 2 columns one is a count and the other is a Sum column.

Sunny Singla

Thanks and sorry for the late reply. Quick question: you said I can create another object which connects to all client records by default - I’m not sure that I follow. How would this default connecting be set up?

Please refer to the screenshots below.

In all the form (create rule)

Then you can check result in combine table like below

for other columns that are the same create a text formula. like {}{}{}

Sunny Singla

1 Like

Thanks, Sunny!