Count how many times a specific number appears in a row

[Dave_k16]

Hello,

I am tracking productivity:

And I’d like to track the number of days that the productivity was above (let’s say) 75%, but happening in an uninterrupted sequence of days

In the image above, the number would be 0 (or 1)

In this example it would be 3

What formula would be used for this? And secondly, where should I write the formula? In contrast to excel, I understand that I can’t pick any random cell to calculate this because each row is connect to that record?

Thanks!

[TheTimeSavingCo]

Hi Dave, I don’t think you can do this with a formula, I’m afraid, and would have to accomplish this with a script of some sort.

Formulas in Airtable are only able to refer to data in a single record, and to get data from other records in it we usually use linked records, and even then, I don’t think we currently have the functionality to run conditional logic on comma separated values

I’ve put a script app together for you here that I think does what you’re looking for, and you should be able to view the code.

Let me know if you need any changes

[Dave_k16]

Hello, this is amazing thanks a lot!

In terms of the calculation, it’s exactly what I am after, and in terms of the use case 2 more things

1. I’d like to have the script running automatically and always evaluating / counting the longest consecutive run

2. The number (in your example 5) to be pulled into a new base/table.

Reason is that I’d like to perform this operation across multiple bases (each base belonging to a different person), pull the highest number of each person/base into a table, and rank order it based on the highest value

I.e. “who is the most productive?” counted as the longest streak of productivity days above certain %

Would that be possible?

Cheers

[TheTimeSavingCo]

Dave.k16:

I’d like to have the script running automatically and always evaluating / counting the longest consecutive run

Hmm, automatically might drain your automation runs really quickly though. How does once a day sound?

Dave.k16:

The number (in your example 5) to be pulled into a new base/table.

Ah hmm, this would be slightly trickier, but yeah, totally doable

If, instead of each person having their own base, each person could have their own table in the same base that would make things a lot easier though. Is it possible to compile all the bases into a single base?

If not, would it be possible to created a synced view for each person’s base and compiled all of those into a single base?

[Alexey_Gusev]

Hi,

it’s possible to do by formula, but hard. Multi-record calculations, with examples that might be helpful, described here