Domo IDEAs Conference - Beast Modes - Running Totals

GrantSmith

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.

GrantSmith

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

mhouston

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

GrantSmith

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.