1. Calculation functions
  2. All Functions
  3. Time and Date Functions
  4. TIMESUM

Aggregates values between two time periods.

You can use the TIMESUM function to aggregate values over time in modules that do not contain a Time dimension.

Syntax

TIMESUM(Line item to aggregate [, Start period] [, End period] [, Aggregation method])
Argument Data type Description
Line item to aggregate (required) Number, Boolean, time period, list, or text The line item to aggregate over a period of time.
Start period (optional) Number, time period The period to start the aggregation from. If omitted, the function aggregates all periods.
End period (optional) Number, time period The end period to finish the aggregation at. If omitted, the function only returns the value for period in the Start period argument.
Aggregation method (optional) Keyword The aggregation method to use.

Returns

The TIMESUM function returns a result of the same format as the Line item to aggregate argument.

As the TIMESUM function only returns

Arguments

Line item to aggregate

The value to aggregate over time. The Line item to aggregate argument can be a line item of any format.

Start period

The optional Start period argument can be a number or a time period. If you use a number for the Start period argument, the function selects a time period relative to the current period chosen in the Model Calendar. A negative number selects a period before the current period, and a positive number a period after the current period. Zero selects the current period.

You can also use a time period-formatted value, or an absolute time reference such as TIME.'Current period' or TIME.'Jan 20' for the Start period argument.

If you omit the Start period argument, the TIMESUM function returns the sum of the Line item to aggregate argument over all periods in the module.

End period

The optional End period argument can be a number or a time period. If you use a number for the End Period argument, the function selects a time period relative to the current period chosen in the Model Calendar. A negative number selects a period before the current period, and a positive number a period after the current period. Zero selects the current period.

You can also use a time period-formatted value, or an absolute time reference such as TIME.'Current period' or TIME.'Jan 20' for the End period argument.

If you omit the End period argument, but provide the Start period argument, the TIMESUM function returns the value of the Line item to aggregate argument for the Start period.

Aggregation method

The keywords for the optional Aggregation method argument are:

  • SUM, which returns the sum
  • AVERAGE, which returns the mean average
  • MIN, which returns the lowest value
  • MAX, which returns the highest value
  • ANY, which returns a value of TRUE if any values of a Boolean-formatted item are TRUE
  • ALL, which returns a value of TRUE if all values of a Boolean-formatted item are TRUE
  • FIRSTNONBLANK, which returns the first non-blank value
  • LASTNONBLANK, which returns the last non-blank value
  • TEXTLIST, which returns all of the values of a text-formatted line item, separated by a comma and a space

Each of the keywords are compatible with the same data types as their equivalent aggregation function.

If you provide the other arguments, but omit the Aggregation method argument, the TIMESUM function uses the SUM aggregation method.

Constraints

The TIMESUM function has these constraints:

  • You can only provide a latter argument if you provide all prior arguments.
  • The result line item must be the same format as the Line item to aggregate argument.
  • The Start period and End period arguments only support number- and time period-formatted values.
  • You must define a Current Period in Model Settings to use a numeric value for the Start period or End period arguments.

Excel equivalent

The TIMESUM function has no Excel equivalent.

Examples

In these examples, Revenue is a number-formatted line item that contains values for each month. The module uses Months for its timescale and the current period selected in the Model Calendar is June 2020.

Example with only first argument

TIMESUM(Revenue)

This example only contains the mandatory Line item to aggregate argument. If you only use the Line item to aggregate argument, the TIMESUM function sums its values over all time periods within the module (excluding the Brought-Forward period, if the module contains one).

This behavior can be useful when you use the Weeks: General timescale, as it does not contain a summary of all periods.

Start period and End period examples

If you provide the Start period argument without the End period argument, the TIMESUM function returns the value from the Start period.

As you define the period that the TIMESUM function applies to within the formula, it's best to use it in modules without a Time dimension to improve performance.

The three formulas below use a number for the Start period argument. A negative number selects a period before the current period, and a positive number a period after the current period. Zero selects the current period. As time progresses and the current period updates, the results that these formulas return update automatically. The current period for these examples is June 2020.

Formula Description

TIMESUM(Revenue, 0)

Returns the value for the Revenue line item from the Current Period, June 2020. It would be more efficient to use the SELECT function for this purpose, for example:

Revenue[SELECT: TIME.'Current Period']

TIMESUM(Revenue, -1)

Returns the value for the Revenue line item from one period before the Current Period, May 2020.

TIMESUM(Revenue, 1)

Returns the value for the Revenue line item from one period after the Current Period, July 2020.

The three formulas below use an absolute time reference for the Start period argument. As the formulas use absolute time references, the results that these formulas return do not update automatically as the current period updates.

Formula

Description

TIMESUM(Revenue, Time.'Jun 20')

Returns the June 2020 value for the Revenue line item.

TIMESUM(Revenue, Time.'May 20')

Returns the May 2020 value for the Revenue line item.

TIMESUM(Revenue, Time.'Jul 20')

Returns the July 2020 value for the Revenue line item.

The formulas below contain both the Start period and End period arguments. As the Aggregation method argument is omitted from the formulas, they use the default aggregation method, SUM.

Formula Description

TIMESUM(Revenue, -11, 0)

This formula returns the sum of the values of the Revenue line item for July 2019, 11 periods earlier, through June 2020, the current period.

TIMESUM(Revenue, 0, 11)

This formula returns the sum of the values of the Revenue line item for June 2020, the current period, through May 2021, 11 periods later.

TIMESUM(Revenue, 0, 2)

This formula returns the sum of the values of the Revenue line item for June 2020, the current period, through August 2020, two periods later.

Aggregation method examples

These examples show the results of the different keywords for the Aggregation method argument.

Two modules named TIMESUM line items and TIMESUM example formulas. The TIMESUM example formulas module contains an example of each keyword for the aggregation method keyword.

The 12 months of 2020 display on columns. The module uses Months for its timescale and the current period selected in the Model Calendar is June 2020.

A number of line items display on rows. These contain the line items the formulas use for the Line item to aggregate argument: Number, Boolean, Date, and Text. They’re each of the same format as their name suggests. The other line items contain the example formulas in the table below.

Formula

Result Description
TIMESUM(TIMESUM line items.Number, 0, 4, SUM)

2,550

This formula aggregates the number-formatted values between the Start period of June 2020 and End period of October 2020, four periods later.

The formula uses the SUM keyword, so it sums the five periods' values of 700, 800, 600, 400, and 50.

TIMESUM(TIMESUM line items.Number, 0, 4, AVERAGE)

510

This formula aggregates the number-formatted values between the Start period of June 2020 and End period of October 2020, four periods later.

The formula uses the AVERAGE keyword, so it calculates and returns the mean average of the five periods' values of 700, 800, 600, 400, and 50.

TIMESUM(TIMESUM line items.Number, 0, 4, MIN)

50

This formula aggregates the number-formatted values between the Start period of June 2020 and End period of October 2020, four periods later.

The formula uses the MIN keyword, so it returns the smallest of the five periods' values of 700, 800, 600, 400, and 50.

TIMESUM(TIMESUM line items.Number, 0, 4, MAX)

800

This formula aggregates the number-formatted values between the Start period of June 2020 and End period of October 2020, four periods later.

The formula uses the MAX keyword, so it returns the largest of the five periods' values of 700, 800, 600, 400, and 50.

TIMESUM(TIMESUM line items.Boolean, -5, 6, ANY)

TRUE

This formula checks the Boolean values between the Start period of January 2020 and End period of December 2020.

The formula uses the ANY keyword, so it checks if any of the Boolean values in this period are TRUE. If any are true, the formula returns a value of TRUE.

TIMESUM(TIMESUM line items.Boolean, -5, 6, ALL)

FALSE

This formula checks the Boolean values between the Start period of January 2020 and End period of December 2020.

The formula uses the ALL keyword, so it checks if all of the Boolean values in this period are TRUE. If all are true, the formula returns a value of TRUE.

TIMESUM(TIMESUM line items.Date, TIME.'Jan 20', TIME.'Dec 20', FIRSTNONBLANK)

9/1/2020

This formula checks the date-formatted values between the Start period of January 2020 and End period of December 2020.

The formula uses absolute time references for the Start period and End period arguments. This means that the formula always uses these values, and does not update as the current period changes.

The formula uses the FIRSTNONBLANK keyword, so it returns the first non-blank value between the Start period and End period.

TIMESUM(TIMESUM line items.Date, TIME.'Jan 20', TIME.'Dec 20', LASTNONBLANK)

1/1/2021

This formula checks the date-formatted values between the Start period of January 2020 and End period of December 2020.

The formula uses absolute time references for the Start period and End period arguments. This means that the formula always uses these values, and does not update as the current period changes.

The formula uses the LASTNONBLANK keyword, so it returns the last non-blank value between the Start period and End period.

TIMESUM(TIMESUM line items.Text, 0, 4, TEXTLIST)

f, g, h, i, j

This formula aggregates the text-formatted values between the Start period of June 2020 and End period of October 2020, four periods later.

The formula uses the TEXTLIST keyword, so it returns all of the text-formatted values between the Start period and End period. The formula inserts a comma and a space between each returned value.

See also