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

  • 23 August 2021
  • 0 replies
  • 6 views

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:

Running Total Function

Syntax

Sum

runsum()

Average

runavg()

Minimum

runmin()

Maximum

runmax()

Variance

runvar()

Standard Deviation

runstdev()

Variance (Uncorrected)

runvarp()

Standard Deviation (Uncorrected)

runstdevp()

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:

24DnP1VX_RlpbchPqbNgF86t7GWkHs1-ImW-j2L6G-lkFKlsnmvg0_dr62Pa_8CSlfnZve1D9qQI07MCZaEX7_rpBAdmBne_gL_M4wUGEUTj6a3lGaHS_LzYXjh4rUVcUNPM-FxH

Diagram 1: LDM of Sales

 

Type

ID

Table 

Column

Fact

order_lines.quantity

Order lines

Quantity

Fact

order_lines.price

Order lines

Price

Attribute

order_lines.status

Order lines

Order status

Date

date.<date granularity>

Date

 

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:

FZwI9Awa35s6DEX2PRC0RwScq_iBFWQRQHlcjVNWcU1qvy144PALdHHxCEd7fUoUgznQXO5-bFyMxvfswggWqZvKxhzX_akJVbKPCIKgd2fQrs3GmxrdCANGZAeo7tpBFWt0gJ9S

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

 

-M1OCs8g0b0p_iWY8DC7O65Etg4GE2S3-G5Tr1yWC0MXS2rlE7ppGvedDTtxvfl8ezTzHX0AMKlmhJOuo0IWTL5Q61_pB5MZ2xsC4KTTWZyaMczBMLgDOFbKHtjNiaN4JbTBAmUT

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: 

i8JcwlQUYxvEFHLf5M0efCSJbeEUuM2ApA7Ne1EXUrqNKUyOtmuK9AoUcLuTRtxJ5l6x97wmeR2CVOwjCjIdDkJwswlvJG3AzRSqIaOLST0tXEVRmkNmUWiPoPhp-mMWC-3-Retf

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:

 

ce-Uds08j7phjPm-6c5UzQJSVG_UbqpcF_AHVzIu7cUfwxAYbXq5AsNjf4tR0orIwLJ_iNdcKScx8YfPQvnPUL7c00KwhnT3PH8QdANiwLwvJ_Re7TgWCxKqgLHfhtXmDT9uDQJq

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:

9LCAFVrJM1jW58quFzVv3dUNDeDryGI5VUvI-TlB91iXh8lmK3O_UQ45iQQBwVV8bywMxAuYgPm6stp9UsdnfKtpBgr7qOS9iE81FBIyT0oQf_9l00eyWTT3qknaqs8tCHMLtgYK

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:

XkZ02UedSK_ptTHGQLernGTftjIPEA__3V-PaIg-yH8ySgKkVadf805uOJYQgTdk6Rev8FidoFRJ-bRcuuVIYrOJYG7PuEv1Bnkx8dP2hGRUt4EDWvKep27fF_wuVbSpE9mgibCD

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/


0 replies

Be the first to reply!

Reply