Calculation inconsistently disconnecting from Date field

Xan · 2023-12-27T00:04:17+00:00

There was an error rendering this rich post.

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?

Find more posts tagged with

Facebook

Date

Beast Mode

Accepted answers

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)

All comments

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!