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.
