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.
How do I calculate a running total?
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.
-- 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 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`)
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.