Calculation inconsistently disconnecting from Date field

Xan
Xan Member
in Beast Mode

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.

Screenshot 2023-12-26 at 4.00.44 PM.png

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?

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Xan
    Xan Member

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

Categories