Running Total Functions
A running total is a calculation based on input values that are known so far.
Examples
In the following table, you can see that a running summation total (RUNSUM) is calculated for the values in the Units column that have occurred up to the current date, yielding a Month to Date (MTD) calculation. When this column is added to a running sum across months, a YTD calculation is available:
| Date | Units | MTD | YTD |
|---|---|---|---|
| 05/28/2014 | 2 | 77 | 191 |
| 05/29/2014 | 3 | 80 | 194 |
| 05/30/2014 | 6 | 86 | 200 |
| 06/02/2014 | 2 | 2 | 202 |
| 06/03/2014 | 3 | 5 | 205 |
| 06/04/2014 | 1 | 6 | 206 |
| 06/05/2014 | 2 | 8 | 208 |
| 06/06/2014 | 3 | 11 | 211 |
| 06/09/2014 | 4 | 15 | 215 |
| 06/10/2014 | 1 | 16 | 216 |
| 06/11/2014 | 3 | 19 | 219 |
In this example, the Running Sum values provide a visualization into the total Units that have been recorded so far for the month and year. Running total functions provide an effective method for creating snapshots of metric values over an interval of date or attribute values.
For example, you can use running totals to identify how Sales are tracking toward expected goals for the reporting period. The following table renames the columns of the previous table and adds new columns to support this Sales example:
| Date | Sales | MTD | Expected MTD | Difference |
|---|---|---|---|---|
| 05/28/2014 | 2 | 77 | 75 | +2 |
| 05/29/2014 | 3 | 80 | 80 | 0 |
| 05/30/2014 | 6 | 86 | 85 | +1 |
| 06/02/2014 | 2 | 2 | 2 | 0 |
| 06/03/2014 | 3 | 5 | 4 | -1 |
| 06/04/2014 | 1 | 6 | 6 | 0 |
| 06/05/2014 | 2 | 8 | 8 | 0 |
| 06/06/2014 | 3 | 11 | 10 | +1 |
| 06/09/2014 | 4 | 15 | 12 | +3 |
| 06/10/2014 | 1 | 16 | 14 | +2 |
| 06/11/2014 | 3 | 19 | 16 | +3 |
Running total functions can be created like any other metric and have broad application across a variety of transactional data.
- Running totals can be computed for multiple types of aggregation functions. Supported running functions include average minimum, maximum, and sum, as well as statistical functions such as standard deviation and variance.
- In addition to date dimensions, some running totals can be applied to known values of attributes through the secondary-click menu.
Using running totals and WITHIN keyword
The WITHIN keyword can be applied to MAQL definitions of running total
metrics to specify the scope within which running totals are maintained.
For example, if you add WITHIN (Quarter) to your MAQL definition for
your running total metric, the total is maintained throughout a quarter
and restarts calculation at the start of the next quarter.