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
else
0
END
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?
Regards,
Comments
-
Hey JYang,
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)
else SUM(0)
END
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 -
Hey all,
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.
Cheers,
0 -
The SUM has to encompass the formula. Should look like this:
SUM(CASE when 'field' < 0 then 'field' else 'field' * 2 end)
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive