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

[Rick2]

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?

[Sunny_Singla]

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.

Regards,
Sunny Singla
ssingla1985@gmail.com
+919855089359

[Rick2]

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?

[Sunny_Singla]

Please refer to the screenshots below.

In all the form (create rule)

Then you can check result in combine table like below

https://roberts.knack.com/farmers#testing-combination

for other columns that are the same create a text formula. like {first.name}{second.name}{third.name}

Thanks,
Sunny Singla

[Rick2]

Thanks, Sunny!