Calculation inconsistently disconnecting from Date field

Xan

Howdy y'all!

What I imagine would be a simple Beast Mode formula is confusing me and my team. In advertising data from Facebook, we are making a new field called 'Test Facebook Engagements' that is the sum of reactions, shares, and comments. Here is the formula:

SUM(ActionsPost)+SUM(ActionsPostReaction)+SUM(ActionsComment)

Shown in the picture below is how some rows will have no value for the calculated field, despite the arguments being present. Furthermore, the total value at the bottom is correct, despite the blank rows above.

I'm seeing this behavior across all accounts and date ranges in this data source. Creating the calculated field on the base data set, in Magic ETL, and the final data set all produce the same buggy result.

How can we fix this?

GrantSmith

When you add a null to a number the result is null. Wrap your SUMs with a COALESCE to return a 0 instead.

COALESCE(SUM(ActionsPost),0)+COALESCE(SUM(ActionsPostReaction),0)+COALESCE(SUM(ActionsComment),0)

GrantSmith

When you add a null to a number the result is null. Wrap your SUMs with a COALESCE to return a 0 instead.

COALESCE(SUM(ActionsPost),0)+COALESCE(SUM(ActionsPostReaction),0)+COALESCE(SUM(ActionsComment),0)

Xan

That did the trick. Thank you for your help and fast response!