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.