Counting NPS detractors from a table containing multiple records per client

1

Hi guys!
I am a new Coda user, just becoming familiar with formulas. Could you please help me with this problem? I have a doc that contains different NPS score entries coming from our clients…this is the structure of the table:

Client | NPS Score | Date entered

Client 1 | 5 | Jun 3
Client 1 | 4 | Jul 15
Client 2 | 9 | Mar 15
Client 2 | 7 | Apr 12
Client 3 | 4 | Jan 12
Client 1 | 7 | Apr 22

I have multiple entries per client, I need a formula to filter those NPS detractors (NPS Score < 7 points) and count them but just taking into account the “last” entries of each client

For instance, in the sample above the formula for counting detractors should triggers this result: 1 => Client 1 (Jul 15)
If I would need to count the promoters (scoring >8), the result should be: 0

The challenge for me is to be sure that I am just counting the last entry of each Client. I was using this formula for calculating the detractors but the result is not accurate, assuming that I need to use a more complex formula

[NPS Table].Filter([NPS Score]<7 AND [Date entered].LAST()).[client Name].CountUnique()

Thanks!!

2

@anon47419885

There are many ways to structure the data and to solve for this, depending on the requirements of the final build out. Here’s a simple illustration to give you some ideas. Review the formulas in the yellow columns.

1 Like
3

Hi @Ander!!

Thanks man! you really solve this. I never thought in a second table. This is great!

1 Like