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.
Comments

Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&index=6
0