Notion Answers

Help between Notion users


Register & Ask

It's free & easy

Get answers

Answers, votes & comments

Vote and select answers

Receive points, vote and give the solution

Question

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.

1 Answer

0vote

polle Points27320

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

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

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

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

https://sheer-fact-ee5.notion.site/e3134f69305c4eb19643e29fd36b0952?v=1de51a3544c6489ba35123f9dbd6885e

0vote

polle commented

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.

1vote

abaginski commented

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

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

1vote

abaginski commented

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

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. :)

Please log in or register to answer this question.

Exclusive for Notion Answers Users

...

Welcome to Notion Answers, where you can ask questions and receive answers from other members of the community.

Please share to grow the Notion Community!

Connect