Help with Beast Mode Formula
I need help doing a complex sum in a beast mode, as when I do it I either get an error or a totally wrong number.
- The formula I was given reduces to
A/(A+B-C)
, and sums across the entire data set for whatever date range the user chooses. - Each of the letter values will be either a zero or one for a given record based on flags in that record: it's quite possible that an individual record will have no flags raised, making the denominator zero for that record.
When I sum up the values for the numerator and denominator separately and manually calculate the results I get the expected results (9.4%, in this case).
If I set my beast mode to SUM(A/(A+B-C))
I get a divide by zero error, as it calculates the formula per record before summing and one or more of the records will have the denominator calculate to zero.
If I do SUM(A) / SUM(A+B+C)
or SUM(A) / (SUM(A) + SUM(B) - SUM(C))
I get 51%, which is totally wrong.
I'm obviously thinking about this wrong, so any perspective adjustments that make it work would be appreciated.
Best Answer
-
What happens if you use a table chart and then create separate beast modes for A, B and C to sum them all together and validate those sums are calculating correctly? Do you get the numbers you're expecting for each of those?
SUM(A) / (SUM(A) + SUM(B) - SUM(C)) should work according to your logic but without knowing the underlying logic being used in the beast mode to calculate the flags it's difficult to pinpoint the exact cause.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Answers
-
Just to be clear each record has a value for A, B and C, which will either be a 1 or 0 since they are basically flags and your card should show a single % based on all the entries that match your filter criteria (including date range).
Can you share the examples of the list of values for A, B and C you're using and how manually you're getting to 9.4%? This will allow us to understand the logic behind how it should be calculated via beast mode, or even better, lets suppose your filtered range would give you these 9 entries:
What would be the result you would expect and can you explain how you're getting to it?
1 -
This is correct. A, B, and C are derived with logic, but are still in the end flags.
Based on your dataset, I would expect to get 7/(7+4-5) = 7/6 = 116.67%
In the actual dataset there are 7.722 million records for the month to date. The sum of flag A for this period is 53,952, the sum of flag B is 547,753, and the sum of flag C is 29,834.
The sum of (A + B - C) is 571,871. Based on the formula A/(A+B-C) I should get a final answer of 9.434% (53,952 / 571,871), but when I use the formula
SUM(A) / SUM(A + B - C)
I get 51.516%; I get the same results when I doSUM(A) / (SUM(A) + SUM(B) - SUM(C))
. I can't doSUM(A / (A + B - C))
because there are records where none of the three flags are raised, so it says "You have a divide by zero error in your Card's Beast Mode".I'm just not seeing what's going on, and it's annoying.
0 -
What happens if you use a table chart and then create separate beast modes for A, B and C to sum them all together and validate those sums are calculating correctly? Do you get the numbers you're expecting for each of those?
SUM(A) / (SUM(A) + SUM(B) - SUM(C)) should work according to your logic but without knowing the underlying logic being used in the beast mode to calculate the flags it's difficult to pinpoint the exact cause.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
I followed your suggestion and converted to a table, used the ticket date for the first column and then put columns for A, B, C, (A+B+C), and the full formula, set all of those to SUM, and the calculations all worked. I was even able to get the summary number to be correct over the selected date range.
Here's a kick in the head: the Powers That Be want it to be a single number so I converted the card back and prepared to let you know it was still broken when, lo and behold, it worked! I saved it and slowly backed away, so now everyone should be happy (except for me: why did this work?!?).
7
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive