Beast Mode: Table Card Total Row Issues
I have a beast mode I'm trying to use that calculates individual performance goals. Using a table card, and including total and subtotal rows.
The first version of the beast mode was: (when sales dollars grow $2500 or more a 1% bonus is paid)
case
when (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))>2500
then (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))*.01
else 0
end
This give the correct result at the row level on the card but not for the subtotals and the total row. The beast mode is evaluating those subtotal and total rows on their own against the 2500 parameter instead of aggregating the individual values. So I changed the beast mode by wrapping the whole thing in a SUM() as follows:
SUM(case
when (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))>2500
then (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))*.01
else 0
end)
This produces the "An issue has occurred during processing. We are unable to complete the request at this time." message and the card will not render. Assuming that because the beast mode has SUM() at multiple layers its creating a conflict. So I removed the SUM() elements from within the case statements as follows:
SUM(case
when (case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end-case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end)>2500
then (case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end-case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end)*.01
else 0
end)
Problem is now that the results are not correct at the row level on the card, but the totals and subtotals are aggregating correctly (based on the incorrect rows).
What am I missing? (Forest for the trees)
Best Answer
-
Hey @swagner
I’m on mobile so this is briefer than normal but because you’re attempting to aggregate on two separate levels which Domo doesn’t necessarily like. You might be able to calculate the sub totals or totals using a window function however I’d recommend using a data view on this data set to calculate the data on a daily level and then do the sub totals or totals on the card.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Answers
-
@swagner Some sample data might help with troubleshooting what is going on here, but I also wonder if considering a different approach might be easier. Have you considered using Magic ETL to get the totals you need? You could use a Group By tile to group by salesperson and year, then use the Rank & Window Lag function to put the previous year's sales total next to the current year's sales totals. You can then use the calculator or formula tile to easily calculate the bonus.
Would that work for you?
**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.1 -
Sample data attached. I am trying to do with beast mode since the actual application is a "one off" analysis and the dataset we're using it 30M plus rows. Hate to create another aggregated dataset for just one card.
0 -
Hey @swagner
I’m on mobile so this is briefer than normal but because you’re attempting to aggregate on two separate levels which Domo doesn’t necessarily like. You might be able to calculate the sub totals or totals using a window function however I’d recommend using a data view on this data set to calculate the data on a daily level and then do the sub totals or totals on the card.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
@GrantSmith I started doing some testing with a window function but it's causing other issues. I agree with what you and @MarkSnodgrass are suggesting here - it's a data side thing I have to do. Thanks for your help. Hope you are doing well.
0 -
@swagner , you can't finish your requirement just using window functions in analyzer.
@GrantSmith is hinting you in the right direction. You're trying to functinally filter on an aggregated value (if the sum of activity in 2019 and 2018 is greater than x then .... )
If you've got 30M rows you can still get away with building the aggregation in Magic (SUM(AMOUNT) GROUP BY Year and Salesrep ) and JOIN to the raw data.
That way you can decide in analyzer whether to include that individual row.
OR do it in a DataSet View (more performant and you don't have to wait for ETL).
This is the extensive explanation, but if you fast forward to where we're playing with DSVs, you'll get a feel for how this can be accomplished. Just don't use a Window function, just do a normal GROUP BY and JOIN back to your data.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 754 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive