Running Total Function in GoodData.CN (Sum vs Runsum)

Running Total Functions

Running Total Functions allow you to keep track of a metric from the beginning of a period until now or calculate the statistics in-betweens rows, such as total revenue year-to-date or sum of revenue between the last 2 periods and now. In GoodData.CN, you may use this function to calculate sum, average, minimum, maximum, variance, and standard deviation with the following syntax:

Table 1: List of Running Total Functions available in GoodData.CN

The Running Total Functions are a great tool to calculate moving averages or accumulated summation. In this article, we will explore how to use the running total function syntax to define metrics in GoodData.CN. We will be using the sample data available in the community edition. The logical data model (LDM) and its selected metadata are below:

Diagram 1: LDM of Sales

Table 2: Selected LDM Metadata of Sales

Sum vs.Runsum

What makes runsum different from sum is that runsum accumulates the summation made in the previous period or dimension. To demonstrate the difference, let's define 2 metrics using both syntaxes to calculate the revenue:

Select sum({fact/order_lines.quantity}*{fact/order_lines.price}) where {label/order_lines.order_status} = \"Delivered\" - Syntax 1

Select runsum({fact/order_lines.quantity}*{fact/order_lines.price})where {label/order_lines.order_status} = \"Delivered\" - Syntax 2

I have named the metrics with Revenue and Total Revenue since First Month for Syntax 1 and Syntax 2, respectively. Let’s put these metrics in a table in Analytical Designer segmented by Date and observe the difference between Syntax 1 and Syntax 2:

Diagram 2: Sum vs. Runsum Sliced by Year

Once the data is segmented, we can see the Total Revenue since First Month accumulates the previous month's revenue. In contrast, Revenue only displays the revenue of the respective month/year. We did not specify the timeframe for Syntax 2 to accumulate the revenue. Therefore, Total Revenue since First Month accumulates the revenue from the first available period, Oct 2017. 

And, of course, it works the same with other statistical metrics. If you are interested in minimum or maximum revenue, replace runsum with runmin or runmax.

Running Total Within a Specific Timeframe

A limitation of Syntax 2 is that the metric includes all historical data; it may be more useful to calculate the given metric within a specific timeframe for reporting purposes. For example, I think the user may be more interested in daily revenue and Year-to-date accumulated daily revenue (Summation of revenue from the beginning of the year to current date); it means we have to modify Syntax 2 only to sum the daily revenue within each year. To do this, we need to specify the timeframe using the keyword within the syntax. In GoodData.CN, it is required to place {label/<date table>.<date granularity>} in the parameter and which only accepts date attributes. Syntax 3 below is the modified syntax for year-to-date revenue:

Select runsum({fact/order_lines.quantity}*{fact/order_lines.price}) within ({label/date.year}) where {label/order_lines.order_status} = \"Delivered\" - Syntax 3

Diagram 3: Revenue and Total Revenue YTD

As we expected, the metrics only accumulate the revenue within each year, resetting the calculation in January 2018. Total Revenue YTD can be segmented in other date granularity lower than Year, like Quarter: 

Diagram 3: Revenue and Total Revenue YTD sliced by Quarter

However, this functionality does not work when segmented by the same date granularity (like year in our example) or any non-chronicle date granularity (Like month of year or day of month).

Rolling Window

GoodData.CN offers the ability to calculate statistics within a window frame, such as moving averages. The format of the rolling window is Select runsum(<attribute/fact/metric>) Rows between <Specific row(s)> and <Specific row(s)>. You may place N preceding, N following, current row to represent N previous rows, N following rows, and current row, respectively. For example, if I would like to have 5 days moving average of revenue (Take an average of the last 4 days and today), I may have the following MAQL:

Select runavg({metric/revenue}) rows between 4 preceding and current row - Syntax 4

This MAQL query takes the average of revenue defined in Syntax 1 for the last 5 days (Including today). Here is what a line chart will look like if we limit the data to only this year:

Diagram 4: Revenue 5 Days Moving Average

The blue line represents the actual daily revenue, while the orange line represents the 5 Days moving average revenue. As we can see in Diagram 4, the orange line is smoothened and displaying the 5 days trend of revenue. You may also combine the within keyword to limit the time frame within a specific date granularity. If we are interested in obtaining the 5 days moving average using only the same year data, we may modify Syntax 4 to the following:

Select runavg({metric/revenue}) within ({label/date.year}) rows between 4 preceding and current row - Syntax 5

Conclusion

Running Total Function is a great way to calculate a metric across a specific time frame. We have explored the difference between sum and runsum and how flexible it is to calculate your metrics with different time frames. 

Syntax format:

Select run___(<metric>) within ({label/<date table>.<date granularity>}) rows between _____ and ____ where {label/<attribute/metric/fact>} = <some condition>

(Tip) Note on Calculating Moving Average

Going back to our example on calculating Revenue 5 days moving average, you may realize the flaw of the MAQL query on Syntax 5 is that the entries of the first 4 days do not fully take 5 days of average (ie, the moving average for the entry on Jan 1, 2020 does is the same as the revenue), like below:

Diagram 5: The flaw of Syntax 5

It happens because the entries needed are filtered out. In order to fix, you need to include “WITH PF Except Date” in Syntax 5, so that the metric is able to include the entries from the previous year in the calculation, with the following Syntax:

Select runavg({metric/revenue})rows between 4 preceding and current row with pf except {label/date.day} - Syntax 6

Once you have utilized the new metrics, it will look like this:

Diagram 6: The 5-days Moving Average with the Correct Calcualtion

Once you have created this insight, be sure to test whether this is the desired metric you would like to display on a dashboard and adjust the filtering dependence between this insight and the dashboard.

Reference

Documentation of Running Total Function: https://www.gooddata.com/developers/cloud-native/doc/1.3/analytics/maql/runing-totals/

If you are interested in GoodData.CN, please contact us.

Alternatively, test a trial version of GoodData Cloud:

Start Your GoodData Analytics Trial — Commitment-Free