I’m working on a Coda doc where I’m tracking habits and their streaks. I need some assistance setting up formulas to calculate and display both the current and longest streaks for each habit. Here’s what I’m aiming to achieve:
- Current Streak: Calculate the streak for each habit up to today’s date.
- Longest Streak: Determine the longest streak for each habit across all recorded dates.
Here’s the structure of my table:
| Name | Date | Streak |
|---|---|---|
| Habit 1 | 4/5/2024 | 1 |
| Habit 1 | 4/6/2024 | 2 |
| Habit 1 | 4/7/2024 | 3 |
| Habit 1 | 4/8/2024 | 4 |
| Habit 1 | 4/9/2024 | 5 |
| Habit 1 | 4/10/2024 | 6 |
| Habit 1 | 4/11/2024 | 7 |
| Habit 1 | 4/12/2024 | 8 |
| Habit 2 | 2/18/2024 | 1 |
| Habit 2 | 2/19/2024 | 2 |
| Habit 2 | 2/20/2024 | 3 |
| Habit 2 | 2/21/2024 | 4 |
| Habit 2 | 2/22/2024 | 5 |
| Habit 2 | 2/23/2024 | 6 |
| Habit 2 | 2/24/2024 | 7 |
| Habit 2 | 2/25/2024 | 8 |
| Habit 2 | 2/26/2024 | 9 |
| Habit 2 | 2/27/2024 | 10 |
| Habit 2 | 2/28/2024 | 11 |
| Habit 2 | 2/29/2024 | 12 |
| Habit 2 | 3/1/2024 | 13 |
| Habit 2 | 3/2/2024 | 14 |
| Habit 1 | 5/21/2024 | 1 |
| Habit 1 | 5/22/2024 | 2 |
| Habit 1 | 5/23/2024 | 3 |
| Habit 1 | 5/24/2024 | 4 |
| Habit 1 | 5/25/2024 | 5 |
I’ve tried the following formulas but need help refining them:
Streak Formula: thisTable.Filter(Name = thisRow.Name AND Date = thisRow.Date - 1).Streak.First().IfBlank(0) + 1
I also want to display the results above the table using text/formula block
How can I dynamically display the current and longest streaks for each habit above the table?
Any help or suggestions would be greatly appreciated!
Thanks in advance!
