Metric Running Totals

You can add running totals to GoodData tables through the secondary-click contextual menu in the Report Editor (see Running Totals on Slice and Dice Dialog).

Using XAE (Extensible Analytics Engine (XAE) - FAQ) you can also add running totals as a MAQL function. The RUNSUM keyword can now be applied to any of the core MAQL functions: SUM, AVG, MIN and MAX to generate running total versions of each of them. Now you can write a running total metric and use the result as any other metric result for further computations.

For example, suppose you want to track total amount of all opportunities that are both closed and won, sliced by Quarter/Year attribute. Our starting report definition might be the following one:

What:

SELECT Amount WHERE Status=Won

How:

Quarter/Year (Snapshot)

In table view, a running total could be inserted using the context menu in the Report Editor. In this case, however, suppose you want to omit the current Won metric and leave in the report just the running total as a bar chart. In the Advanced Metric Editor, you can create a new metric using the new RUNSUM MAQL function, as

SELECT RUNSUM(Won)

When the new metrics is saved as RunWon, the results are as follows:

For comparison purposes, the Running Total was added using the secondary-click contextual menu and displayed as a column named Running Sum. The Running Sum (generated via context menu) and RunWon (via metric) columns are identical. If you remove the Running Sum and the Won metrics and switch the report into a bar chart, here are the results, showing only the new RunWon metric:

Suppose you want to display only the year 2011, and the metric should not start the running total computation from 0. Switch back to the table view of the report. Apply the report filter through the Filter tab to keep only the quarters for years 2011 and 2012. Create the filter as Quarter/Year (Snapshot) and make sure the value is not Q4/2010.

In the above image, you can see the before and after results of applying the year filtering. Look at the blue box. The left side of the picture shows the cumulative value after adding Q4/2010 and Q1/2011. On the right side, a filter is applied and the running total starts with the value of Q1/2011. The metric running totals works normally with filters.

If you need to compute from a previous time period (here, Quarter), you must add to the RunWon metric the “without parent filter” clause.

SELECT RUNSUM(Won) WITHOUT PF

The AVG, MAX and MIN functions are also available as running functions.