MicroStrategy ONE
Running and Moving Sums and Averages
These functions include:
All these functions are similar and are called OLAP functions in the Metric Editor. The running sum function uses a metric as input and calculates a cumulative total of values based on a sort order specified in the metric definition. The sort order is not based on the report level. For example, a report with dates, revenue, and month-to-date revenue is needed. The month-to-date revenue is defined as RunningSum<BreakBy={[Month of Year]}, SortBy= (Day) >(Revenue). The Break-by value is Month of Year, so that the running sum is restarted for each month. The running sum is sorted by Day, so that the first day of the month is displayed first, then the sum of the first and second day, then the sum of the first three days, and so on. Finally, the definition includes the metric that the running sum uses.
The moving sum/average is basically a running sum/average with specific start and end times.
The following document contains both a running sum and a moving sum, both calculated on the Revenue metric. The running sum begins at the first quarter, and adds every quarter after that. The moving sum calculates a year-to-date revenue amount, resetting at each new year. Therefore, the moving sum restarts for each year. The running sum's definition is RunningSum<SortBy= (Year, Quarter) >(Revenue), while the moving sum is defined as MovingSum<BreakBy={Year}>(Revenue, 4.0), where 4.0 is the number of quarters to calculate.
The image above shows a MicroStrategy document. For more information on working with documents, see the Document and Dashboard Analysis Guide; for steps to create documents, see the Document Creation Help