Beast Mode Formula is not working as expected
BEAST MODE - % SPENT
(IFNULL(case when `SourceCode` = 'AP' then `TransactionAmt` end,0))
/
(IFNULL(`ProjTotalCostEOJ 1`,0))
fyi - Transaction Amount = JTD and ProjTotalCostEOJ1 is a column brought in VIA ETL
Best Answer
-
Mark, Yoou got me on the right path. This is the Beast Mode that ended up working. I needed to put a MAX in front of the PROJTOTALCOST because that was an already aggregated amount via the ETL. Thanks for your help.
(SUM(IFNULL(case when
SourceCode
= 'AP' thenTransactionAmt
end,0)))/
(MAX(IFNULL(
ProjTotalCostEOJ 1
,0)))0
Answers
-
In your table, did you select an aggregation of sum for the Projected Cost and JTD columns? If so, you would need to incorporate SUM into your beast modes, you would need to do it outside each of your IFNULL statements.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
SUM for RevisedEstimatedCost and JTD were pre-selected as these are aggregated via a GROUP BY in an ETL. This being the case why do I need to add SUM in front of my IFNULL Statements? I'm confused.
0 -
Sorry, I should have been more clear. I was referring to your card configuration in Analyzer. In Analyzer, do you have any of those fields in your card set to Sum? Or is the aggregation set to None. In Analyzer, if you have fields set to aggregate, you need to make sure your beast modes are doing the same thing.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
No you were clear. Yes both of those Fields were pre-selected to SUM
0 -
Try editing your beast mode to look like this:
SUM(IFNULL(case when
SourceCode
= 'AP' thenTransactionAmt
end,0))/
SUM(IFNULL(
ProjTotalCostEOJ 1
,0))**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
That did not work. it gave me zero % across the board.
0 -
0
-
Here is the data coming across FLAT (NO PIVOT TABLE).
I would think my Beast Mode would need to look like this but this does not work either.
SUM(IFNULL(case when
SourceCode
= 'AP' thenTransactionAmt
end,0))
/
MAX(IFNULL(ProjTotalCostEOJ 1
,0))0 -
Doing those types of calculations in a pivot table are tricky. I would try to get it working in a regular table by breaking out the individual components of this beast mode and verifying that you are getting what you expect on each row.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
As a best practice I try to create separate beast modes for the numerator and denominator, and see if those are returning the values I'd expect, so I can easily troubleshoot from there to see whether there are flaws in the logic or where are things going eerie. Since all of them go to 0, it's likely the numerator is returning 0, so either
Transaction Amount
is returning 0 at all times or there are no entries with values for whenSourceCode
= 'AP', but for that you'd need to look at your source data (you can enable the Data table panel in the card to see what is being return and include those columns to better understand what might be going on)
I see the codes on the flat data you shared do not align with the codes on the pivot version you shared first, but at first glance the Beast Mode doesn't look odd.1 -
Mark, Yoou got me on the right path. This is the Beast Mode that ended up working. I needed to put a MAX in front of the PROJTOTALCOST because that was an already aggregated amount via the ETL. Thanks for your help.
(SUM(IFNULL(case when
SourceCode
= 'AP' thenTransactionAmt
end,0)))/
(MAX(IFNULL(
ProjTotalCostEOJ 1
,0)))0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive