Notion has limited support for counting rows in a table using rollups. Unfortunately rollups don’t allow you to count by any other grouping (like month) out of the box (ala group by in sql). Instead you need to prepare your database by normalizing the data, extracting grouping data using a formula, and then modifying the rollup criteria.
Normalizing the field you want to group by
Let’s say you have a list of tasks that have different types and you want to count how many were completed by month.
You might start with a single database that looks like this (where Task type is a select column).
| Task name | Task type | Date completed |
|---|---|---|
| Todo 1 | Writing | 2021-07-05 |
| Todo 2 | Chores | 2021-07-12 |
| Todo 3 | Reading | 2021-07-18 |
| Todo 4 | Reading | 2021-08-02 |
Now you want to count but a rollup field won’t work. You need normalize the data by changing Task type into a relational column that points to another database.
| Task type name |
|---|
| Writing |
| Chores |
| Reading |
Now change the Task type column from a select column to a relational column that points to the Task Type database.
To count the number of tasks by type, add a rollup column where the relation is the Tasks database, the property is Task name, and choose count all.
| Task type name | Count |
|---|---|
| Writing | 1 |
| Chores | 1 |
| Reading | 2 |
Now there is a count for each task type. However, we need to add a few more columns if we want to count by month.
Extract the month using a formula
Add a formula column named ‘Month’ to the Tasks database to extract the month from the date completed.
if(empty(prop("Date completed")), "", formatDate(prop("Date completed"), "YYYY-MM"))
Now you will see this:
| Task name | Task type | Date completed | Month |
|---|---|---|---|
| Todo 1 | Writing | 2021-07-05 | 2021-07 |
| Todo 2 | Chores | 2021-07-12 | 2021-07 |
| Todo 3 | Reading | 2021-07-18 | 2021-07 |
| Todo 4 | Reading | 2021-08-02 | 2021-08 |
Counting by task type by month
This is the tricky part needed to work around the limitations of rollups. Add another formula column named ‘July 2021’ to the Tasks database that will indicate if the task was completed in that month.
Note: You will need to add a column for each month because rollups don’t allow grouping. Yes it’s very manual, but it works.
prop("Month") == "2021-07"
| Task name | Task type | Date completed | Month | July 2021 |
|---|---|---|---|---|
| Todo 1 | Writing | 2021-07-05 | 2021-07 | [✓] |
| Todo 2 | Chores | 2021-07-12 | 2021-07 | [✓] |
| Todo 3 | Reading | 2021-07-18 | 2021-07 | [✓] |
| Todo 4 | Reading | 2021-08-02 | 2021-08 | [ ] |
Now in the Task type database, we can count the number of tasks completed in July by adding rollup column (let’s name it ‘July’) where the relation is the Tasks database, the property is ‘July 2021’, and choose count ‘Checked’.
The result should look like this:
| Task type name | Count | July |
|---|---|---|
| Writing | 1 | 1 |
| Chores | 1 | 1 |
| Reading | 2 | 1 |
Now we have a count by type by month! You can follow this pattern to add more rollups for each month.