Domo IDEAs Conference - Beast Modes - Running Totals
Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to calculate a grand total, running total overall and by month.
Problem:
How do I calculate a running total?
Solution:
We can utilize a window function which will calculate some metrics across the entire dataset (or a subset / partition of it) and return that value for each row.
You'll notice that there is a SUM(SUM(..)) call which may seem odd. Traditional SQL only has a single aggregate function. Because the way Domo works if there is another column being aggregated that aggregation happens after the beast mode. So because a single value is being returned Domo is attempting to aggregate it but doesn't know how to so it'll throw an error if there isn't a second aggregation.
Grand Total:
-- Author: -- Created: -- Last Modified: -- Description: Using a window function return the grand total for the entire dataset -- Will return this value for each row. SUM(SUM(`random_number`)) OVER ()
Running Total:
Running totals require the ORDER BY clause to know how to order the data when doing a running total.
-- Author: -- Created: -- Last Modified: -- Description: Get the running total over the entire dataset. (Not bucketed / partitioned) SUM(SUM(`random_number`)) OVER (ORDER BY `dt`)
Running Total By Month:
This utilizes the PARTITION BY
function to specify the group in which to perform the running total.
-- Author: -- Created: -- Last Modified: -- Description: Calculate the monthly running totals. -- PARTITION BY states how to bucket the information, in this case each month and year. -- ORDER BY tells the order in performing the running calculation -- Without the ORDER BY it would just return the total for the entire month on each row SUM(SUM(`random_number`)) OVER (PARTITION BY YEAR(`dt`), MONTH(`dt`) ORDER BY `dt`)
Note:
Window functions are not enabled by default and require this feature be switched on in your instance. Talk with your CSM to get it enabled.
**Did this solve your problem? Accept it as a solution!**
Comments
-
Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&index=6
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
@GrantSmith have you run into any issues getting these beast modes to work correctly with the analyzer date filter (i.e. this year graph by week/month etc)?
I'm trying to add a running percent of total line to a chart (running total of x/running total of y) following this and the values are correct as long as I'm not using any of the analyzer's group by options in the date range.
0 -
This isn't compatible with the group by options in the date range because we're defining how we want to do the grouping in the window function with the PARTITION BY section.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
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