Search

MOVINGSUM

This function calculates moving values, including moving sum and moving average. It requires a time dimension, because the calculated aggregate values are always relative to the period that contains the result. In this way, the calculated values the function returns can vary over time and can show a different value in each subsequent result time span.

Syntax

MOVINGSUM(x,[f, t, m]) 

where:

  • x: The line item that varies over time.
  • f: From: Number of periods relative to the result period, within the line item's timescale, at which to start aggregation, defined in whole periods.
  • t: To: Number of periods relative to the result period, within the line item's timescale, at which to end aggregation, defined in whole periods.
  • m: Mode: Aggregation method.

If the mode parameter is omitted, MOVINGSUM will use a default aggregation based on the line item data type. Only the line item (x) is mandatory, all other parameters are optional. However, missing intermediate parameters are not permitted.

If a parameter is omitted, as opposed to entered with a zero value, MOVINGSUM will behave as follows:

MOVINGSUM(Revenue) 

Sums all periods, excluding the brought forward period, if one exists.

MOVINGSUM(Revenue,0) 

Returns just the current period. If f is set but t is omitted, then t uses the same value as f.

MOVINGSUM(Revenue,-2,0)

Sums the last 3 periods including the period of the result.

The following examples are not permitted, since intermediate parameters are omitted:

MOVINGSUM(Revenue,,0)
MOVINGSUM(Revenues,,,AVERAGE)   

Back to Top

Format

Input Format Output Format

x: Line item - number, Boolean, date, list, text - Expression is not permitted

f: Integer number - decimal fractions are rounded

t: Integer number - decimal fractions are rounded

m: Mode: All aggregations are available, but some combinations of aggregation method and data type will not work, refer to the arguments section below. The default aggregation based on the line item data type.

  • NUMBER: 'SUM'
  • BOOLEAN: 'ANY'
  • DATE: 'FIRSTNONBLANK'
  • LIST: 'FIRSTNONBLANK'
  • TEXT: 'FIRSTNONBLANK'

Matches the data format used by argument n

Back to Top

Arguments

The function uses the following arguments:

  • x: Line item: Numeric, Boolean, date, time period, list, text
  • f: Number: Numeric line item, property, or expression
  • t: Number: Numeric line item, property, or expression
  • m: Keyword:
    • SUM: Total the values in the defined span
    • AVERAGE: Total the values then divide by the number of values
    • MIN: Returns the lowest value in the defined span
    • MAX: Returns the highest value in the defined span
    • ANY: Checks Boolean values in the defined range and returns TRUE if ANY are positive
    • ALL: Checks Boolean values in the defined range and returns TRUE if ALL are positive
    • FIRSTNONBLANK: Checks date, list, or text values and returns the first non-blank cell in the defined span
    • LASTNONBLANK: Checks date, list, or text values and returns the last non-blank cell in the defined span
    • TEXTLIST: Returns a text string that concatenates the text from the periods in the defined span

Back to Top

Constraints

The function has the following constraints:

  • To and From values: Decimal numbers are rounded to the nearest integer.
  • The time range providing arguments to a line item must match the time range for the result line item. Any formula that conflicts with this rule will be rejected. For more, see Search.
  • A non-time series function, used as a sub-expression of this function, must use the same time range as the line item to which the formula is applied. Combining time series functions and non-time series functions with a time range could return an unexpected result or an error message. For more, see Time Ranges and Time Series Functions.
  • Time Period offsets: Period offsets are determined by the time granularity of the result period. Granularity is a time subdivision at which you can enter data or to which you can aggregate results. Anaplan granularity uses units of increasing size of Day, Week, Month, Quarter, and Years.
  • The function will ignore any periods you define in a To/From span that extend beyond the model timescale range;
      • Extending beyond Timescale: The function will use data from all the available periods to complete the calculation.
        • For example, if From = -11 where the current period is month 3 of the model, then MOVINGSUM would calculate for the entered Aggregate as far as the first period and ignore periods outside the model timescale.
      • Extending beyond Timescale with AVERAGE: The function will calculate the number of time periods within the bounds of the timescale to use as the divisor for averaging, not the number in the entire From/To time span.
        • For example, if the result time period granularity is set for months and the timescale is 2011, a MOVINGSUM(Revenue,-5,0,AVERAGE) calculation will use a divisor of 2 for the averaging when Feb 11 is the result period - only when May 11 is the result period will 5 be used as divisor for the averaging.

Note: If you have Brought Forward active on the line item, the brought forward period will be included in the aggregation.
The time granularity between the source module and results module must match for the function to operate correctly. For example, a source that uses Quarters with MOVINGSUM result in Months will not work.

Back to Top

Excel equivalent

  • No Excel equivalent

Back to Top

Example

MOVINGSUM(Revenue,-2,0,SUM)
  • Sums the last 3 periods including the period of the result, where period is the time granularity of the line item.
MOVINGSUM(Daily Revenue,-2,0,SUM)
  • Where the result is by month, Sums the last 3 months including the result month. Note that the source Daily Revenue could be in days, but the From and To parameters are determined by the result time period.
MOVINGSUM(Revenue,-2,0,AVERAGE)
  • Calculates the moving Average of the previous 3 periods including the period of the result.
MOVINGSUM(Revenue,-2,0,MIN)
  • Returns the minimum value of the previous 3 periods including the period of the result.
MOVINGSUM(Revenue,-1,1,AVERAGE)
  • Calculates the average of the previous period, the period of the result, and the next period.
MOVINGSUM(Flag line item,-1,1,ANY)
  • Where the result is Boolean, returns TRUE, if any one of the previous period, the period of the result, or the next period, is TRUE.
MOVINGSUM(Flag,-1,1,ALL)
  • Where the result is Boolean, returns TRUE, if all of the previous period, the period of the result, and the next period are TRUE.
MOVINGSUM(list item,-1,1,FIRSTNONBLANK)
  • Where the result is date, list, or text, returns the first non-blank cell of the previous period, the period of the result, and the next period.
MOVINGSUM(text,-1,1,TEXTLIST)
  • Where the result is text, returns a text string that concatenates the text from the previous period, the period of the result, and the next period.

Example

In this example, MOVINGSUM is used to average 3 monthly amounts (x) including the result month:

MOVINGSUM(x,-2,0,AVERAGE)

Example using time period

Syntax:

MOVINGSUM(Item,From,To,Aggregate)

Item: The line item that varies over time.

From, To: Define the time span for the MOVINGSUM calculation. From and To are always relative to the result time period; 0 means the result period; -11 means 11 periods before this; -1 would refer to the previous period; +1 would refer to the next period.

Back to Top

Similar functions