Calculation inconsistently disconnecting from Date field
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?
Best 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!**1
Answers
-
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!**1 -
That did the trick. Thank you for your help and fast response!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 601 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 689 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 385 Distribute
- 110 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 112 Manage
- 109 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive