MicroStrategy ONE

OLAPSum

Returns the summation of the current value, preceding values, and succeeding values as defined in the function arguments. Unlike RunningSum and MovingSum, which can only include values above the current row in the calculation, you can use OLAPSum to include values both above and below the current row in the calculation.

The calculations can be restarted based on attributes defined in the function parameters.

Syntax

OLAPSum<Distinct, OLAPWinStType, OLAPWinStOffset, OLAPWinEndType, OLAPWinEndOffset, BreakBy, SortBy>(Argument)

Where:

  • Distinct is a TRUE/FALSE parameter that allows you to use all values in the calculation or to calculate using only the unique values. If you define Distinct to be true, then the parameters OLAPWinStType, OLAPWinStOffset, OLAPWinEndType, OLAPWinEndOffset, and SortBy are ignored.
  • BreakBy is the attribute indicating where the calculation restarts.
  • SortBy is the attribute or metric by which the data is sorted.
  • OLAPWinStType defines the window type for the starting location of the calculation. Select one of the following options, listed by the name of the setting and its corresponding OLAPWinStType value in parentheses:
    • Top of data set (0): The calculation starts at the top value as determined by the BreakBy and SortBy values.
    • Current row (2): The calculation starts at the current row.
    • N rows before current row (3): The calculation starts a number of rows before the current row. You define this offset of rows with the OLAPWinStOffset parameter described below.
    • N rows after current row (4): The calculation starts a number of rows after the current row. You define this offset of rows with the OLAPWinStOffset parameter described below.
  • OLAPWinStOffset defines the offset of rows from the current row to start the calculation. You can define this offset if the OLAPWinStType parameter is defined as N rows before current row (3) or N rows after current row (4).
  • OLAPWinEndType defines the window type for the ending location of the calculation. Select one of the following options, listed by the name of the setting and its corresponding OLAPWinEndType value in parentheses:
    • Bottom of data set (1): The calculation stops at the bottom value. The top value is determined by the BreakBy and SortBy values.
    • Current row (2): The calculation stops at the current row.
    • N rows before current row (3): The calculation stops a number of rows before the current row. You define this offset of rows with the OLAPWinEndOffset parameter described below.
    • N rows after current row (4): The calculation stops a number of rows after the current row. You define this offset of rows with the OLAPWinEndOffset parameter described below.
  • OLAPWinEndOffset defines the offset of rows from the current row to stop the calculation. You can define this offset if the OLAPWinEndType parameter is defined as N rows before current row (3) or N rows after current row (4).
  • Argument is a metric representing a list of numbers.

The function is not valid if your starting point is at a lower point than your ending point.

Example

An OLAPSum Unit Cost metric is created using the OLAPSum function based on the Unit Cost metric, as defined below:

OLAPSum<OLAPWinStType=3, OLAPWinStOffset=3, OLAPWinEndType=4, OLAPWinEndOffset=2, BreakBy={Category} SortBy={Subcategory}>([Unit Cost])

The starting point for the summation is defined as three rows before the current row (OLAPWinStType=3, OLAPWinStOffset=3). The stopping point for the summation is defined as two rows after the current row (OLAPWinEndType=4, OLAPWinEndOffset=2).

This metric is displayed on a report along with Category, Subcategory, and Unit Cost, as shown below.

There are a few facts about this data to take note of.

The first value of OLAPSum Unit Cost is $30.82. This is calculated by adding $13.93, $10.75, and $6.13. These are included because the calculation ends two rows after the current row. Even though the calculation starts three rows before the current row, there is no data above the current row to include in the calculation.

The data displayed on the report is rounded to the nearest cent, which can give the impression that some calculations are slightly incorrect. For example, adding $13.93, $10.75, and $6.13 actually totals $30.81 rather than $30.82 as displayed on the report. This difference is because the data is rounded up for display on the report. You can display more decimal values for the Unit Cost and OLAPSum Unit Cost metrics to see the exact values.

The fourth value for OLAPSum Unit Cost is the only value that can include data from all three rows above the current row to two rows below the current row in the calculation ($13.93 + $10.75 + $6.13 + $7.20 + $25.93 + $9.43 = $73.38).

The final OLAPSum Unit Cost value for the Books Category can only include the Unit Cost value for the current row and the three rows above it ($6.13 + $7.20 + $25.93 + $9.43 = $48.70). It cannot include two rows below the current row because the calculation restarts for the first Subcategory of the next Category. The calculation restarts because the function is defined to break by the Category attribute.