Beast Mode SUM Aggregation Workaround
I'm trying to structure a basic formula in Beastmode to calculate the total sum of a sales figure within a time range and then apply a multiplcation ONLY if it's positive.
Dataset example (daily sales forecast):
1/1/2016 - $10
1/2/2016 - $-30
1/3/2016 - $40
Beast mode example:
WHEN SUM(Sales forecast) > 0 then SUM(Sales forecast)*2
However, what's happening is that the beast mode is applying this formula for each individual daily entry.
E.g. The entire sum of the sales forecast for January 2016 is -$100. When applying the above beast mode, it SHOULD be 0. However, it's still returning a positive number (e.g. $400) because it's applying the logic individually to each daily value (e.g. if 1/1/2016 is <0 then 0, else *2).
Is there any workaround for the beast mode to be dynamic in this way?
I did some testing to see if I could find a way to get around the row by row functionality of beast mode, and I definitely see the issue with what you're trying, and I unfortunately wasn't able to find a way to do what you need in a beast mode.
This may not be what you want to hear, but I would create a dataset in an ETL or Dataflow that rolls the data up by year-month. That way you can use your case statement beast mode with an already aggregated value as opposed to trying to aggregate the data multiple times in beast mode.
Best of luck!**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'1
Mason's correct, comparing aggregations to individual values doesn't usually work. I've see aggregations in case statements work, but only isolated incidents and never comparing against row values.
Just a shot in the dark here, but can you try aggregating each side of your comparisons in your logic, so everything is an aggregation?
WHEN SUM(Sales forecast) > SUM(0)
then SUM(Sales forecast)*AVG(2)
Mathematically that should be the same. Let us know if that works.Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Thanks for the input. Unfortunately, the suggested solution doesn't work as well.
The point about creating monthly summaries is a good workaround, but it makes it limited to 'static' aggregates (e.g. monthly, weekly, etc). Whereas what I had in mind was a more dynamic solution (e.g. any customizable time range).
Oh well. This may just end up being a manual effort instead of automated.
The SUM has to encompass the formula. Should look like this:
SUM(CASE when 'field' < 0 then 'field' else 'field' * 2 end)0
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 195 Visualize
- 253 Beast Mode
- 2.1K Charting
- 11 Variables
- 81 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive