Help between Notion users

It's free & easy

Receive points, vote and give the solution

1vote

# Notion formula fails with rollups

I have a database that calculates a running total. The database has the following properties:

• Name (Title)
• Minutes Worked (Number)
• Previous Item (Relation - refers back to the same database)
• Previous Total Minutes Worked (Rollup - pulls the value of “Total Minutes Worked” from the item selected in “Previous Item”)
• Total Minutes Worked (Formula): prop(“Minutes Worked”) + toNumber(prop(“Previous Total Minutes Worked”))

This works properly for about 6 entires, then the “Total Minutes Worked” fails.

For Example:

``````Name — Minutes Worked — Previous Item — Previous Total Minutes Worked — Total Minutes Worked
A    - 263            - (blank)       - (blank)                       - 263
B    - 88             - A             - 263                           - 351
C    - 55             - B             - 351                           - 406
D    - 423            - C             - 406                           - 829
E    - 160            - D             - 829                           - 989
F    - 82             - E             - 989                           - 1071
G    - 218            - F             - 1071                          - 1026``````

Line G does does not add the numbers properly. The last column should be 1289.

Any suggestions? Thank you.

0vote

polle Points79040

You need to separate the `toNumber` formula in a new column.

Create a new formula column (Previous Total Minutes Worked No) with:

``toNumber(prop("Previous Total Minutes Worked"))``

Then in your Total Minutes Worked formula use:

``prop("Minutes Worked") + prop("Previous Total Minutes Worked No")``

That will work as expected.

1vote

### abaginski commented Jun 12, 2022

Interesting… But it still does something odd:

``````Previous Total Minutes Worked - Previous Total Minutes Worked No
263                           - 263
351                           - 351
406                           - 406
829                           - 566
726                           - 638
720                           - 665``````

Why are the 2 columns different? They should be the same. The first 3 are correct, then they differ. This explains the “incorrect” addition from the original question, but why does the `toNumber` not convert this properly?

0vote

### polle commented Jun 12, 2022

Without seeing the complete database and formulas is hard to tell. There must be something out of place, because the previous formulas should work without errors and should be equal, because it is just converting it to a number.

Review the formulas and properties again to catch where the problem is. Also if you want to share a duplicate to see it, I can take a look.

1vote

### abaginski commented Jun 12, 2022

Here is the link to the complete database. Any suggestions are appreciated. It makes no sense to me!

0vote

### polle commented Jun 12, 2022

The problem is in your "Previous Total Minutes Worked" Rollup.

You are selecting "Total Minutes Worked" and you should select "Minutes Worked" instead, because it is the data you need from them previous item. The total one is only for the last result and not for the Rollup.

0vote

### abaginski commented Jun 12, 2022

Thank you. That does correct the math error, but it does not give me a running total. With this change, the “Total Minutes Worked” field only gives a sum of the current and previous item. I am looking for the “Total Minutes Worked” property to be a running sum of all “Minutes Worked” up to and including the current entry, as in lines A-F from my original question.

0vote

### polle commented Jun 13, 2022

At the bottom of the column where it says calculate, select SUM to see that info.

1vote

### abaginski commented Jun 13, 2022

Thank you so much for all your help with this. The SUM at the bottom does give me that info, but not as a running total in each row. I am also not able to use that total in other calculations, such as changing it to hours:minutes or determining if the individual had met or exceeded pre-arranged minimums.

It also does not address the original issue of the rollup working for the first 5 lines and then failing or the difference between the rollup and the toNumber column.

0vote

### polle commented Jun 14, 2022

The total for each row is: Total Minutes Worked
The total for the column is: SUM

I believe that the previous info and formulas solves the main question "Notion formula fails with rollups".

The other things you are asking for are completely different from this, please select the answer to mark this question as solved and open new ones as needed.

1- I see the rollup working fine for all columns. It is pulling correctly the previous minutes.

Keep in mind that you need to add formulas and rollups in the correct order for things to work. You can't call information from a formula that happens after and as a final calculation to be used before that calculation itself (if that makes sense). You will need an extra column for that.

2- For using the SUM, you will need an extra step as well, like another rollup or an extra database to pull all the information grouped.

3- Converting to hours:minutes can be done with a formula, but that is also another topic.

Hope that helps. :)

...