Sum not functioning when using blank inline field


I am trying to create a dataview that will insert a list of all birthdays or events occuring on the specific day of a note, where I have one note representing each day of the year.
I would also like it to calculate how many years since the birth/event.

However there is an issue when the year from the person is blank.

Things I have tried

My current function is as follows

> AS "Person",
>	BirthYear AS "Birth Year",
>	sum(nonnull(2022-BirthYear)) AS "Age"
FROM "People"
WHERE Birthday = [[November-29]]

All notes within the people folder have the following



When not including the sum function or when the BirthYear has a number then the table outputs correctly.
Additionally if someone has the same birthdate such as November-29 as above and has a birth year then the table will produce that persons result but not someone without a birth year.

What I’m trying to do

I’d like a way to keep the sum function in place and ignore the calculation or produce some sort of error result if the BirthYear is missing.

That way I can either update the person note if I have the information or just leave it as is but still know when to celebrate the event.

In first place, can you explain why you use this expression?

  • sum what? if BirthYear value exists the difference calculation result is only one number! you want to sum it with what?
  • nonnull(2022-BirthYear) - if a problem with a null values, that doesn’t happen with “2022-BirthYear” (a difference between two numbers); it happens with BirthYear

In addition to answering the valid questions of @mnvwvnm , you might afterwards be interested in looking at Functions - Dataview, and in particular the default() function.


Thank you both for these responses.

About sum, this is an error of my interpretation I didn’t realise the equation would run without it and didn’t think to test. Thank you for pointing me to this.

Removing nonnull gave me an result if there was something in the BirthYear inline field however still presented me with a error when that was blank.

With @holroy direction to the default() I was able to resolve the null error.

My function is now

	BirthYear AS "Birth Year",
	2022-default(BirthYear, 0) AS "Age"
FROM "People"
WHERE Birthday = [[November-29]]

which if BirthYear is blank returns the BirthYear column empty and the Age column as current year.

Thank you both for your help. I’ll be sure to test things in more ways in future and take some time to look through dataview documentation more.

An extra to explore:

  • instead of 2022-default(BirthYear, 0) AS Age you can try this (a complement to test another output):
choice(BirthYear, 2022-default(BirthYear, 0), "*unknow*") AS Age

Brilliant Thank you. That was going to be my next bit of research, really appreciate the extra info.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.