MicroStrategy ONE
RunningSum
This function returns the running sum of the values in a list, adding the current value to the preceding values. It can be used to maintain running totals of values in specific categories as well as an entire set. The calculation can restart based on attributes identified in the parameter settings. This is an OLAP function.
Syntax
RunningSum <BreakBy,SortBy>(Argument)
Where:
BreakByis the parameter that sets the attribute designating where the calculation restarts.SortByis the parameter that sets the attribute or metric by which the data is sorted.Argumentis a metric representing the list of numbers.
Expression
Example
Example 1: A running sum displays the results of each calculation as it works towards the total of a set of values. Using the value set (1, 2, 3, 4, 5), the following table illustrates the running sum and its calculations.
|
Values |
RunningSum |
|
1 |
1 (0+1) |
|
2 |
3 (1+2) |
|
3 |
6 (3+3) |
|
4 |
10 (6+4) |
|
5 |
15 (10+5) |
Example 2: This example shows a report where the running sum of the revenue is calculated. The calculation starts over for each region, and the information is sorted within the region by state in ascending order. The report contains the attributes Customer Region and Customer State, and the metrics Total Revenue and Running Sum. A report filter limits data to the Southwest, Southeast, and Northwest regions. The definition of the Running Sum metric is as follows:
RunningSum<BreakBy={[Customer Region]}, SortBy=<[Customer State])>([Total Revenue])
Note that the subtotaled Total Revenue is equal to the last value in the Running Sum column for each region.
