1. Calculation functions
  2. All functions
  3. Time and date functions
  4. TIMESUM

The TIMESUM function aggregates values between two time periods and returns a single value.

For example, you can use the TIMESUM function to aggregate the revenue for previous periods through to the current period.

Syntax

TIMESUM(Line item to aggregate [, Start period] [, End period] [, Aggregation method])

Arguments

ArgumentData typeDescription
Line item to aggregate (required)

Number, Boolean, date, list, or text

This argument must be a line item.

The line item to aggregate over a period of time.

This line item must have Time as a dimension.

Start periodNumber, date, time period

The period to start the aggregation from. This period is included in the aggregation.

If omitted, the function aggregates all periods.

End periodNumber, date, time period

The end period to finish the aggregation at. This period is included in the aggregation.

If omitted, the function only returns the value for the period in the Start period argument.

Aggregation methodKeyword

The aggregation method to use.

The available keywords are SUM, AVERAGE, MIN, MAX, ANY, ALL, FIRSTNONBLANK, LASTNONBLANK, and TEXTLIST. There's more information below.

The default behavior if omitted depends upon the data type of the Line item to aggregate argument.

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

Aggregation method keywords

KeywordCompatible data typesDescription
SUMNumberReturns the sum of the values.
AVERAGENumberReturns the mean average of the values.
MINNumber, date

For numbers, returns the lowest value.

For dates, returns the earliest value (a blank cell is considered to be as early as possible).

MAXNumber, date

For numbers, returns the highest value.

For dates, returns the most recent value.

ANYBooleanReturns a value of TRUE if any values of a Boolean-formatted line item are TRUE.
ALLBooleanReturns a value of TRUE if all values of a Boolean-formatted line item are TRUE.
FIRSTNONBLANKNumber, date, list, or textReturns the first non-blank value for all data types.
LASTNONBLANKNumber, date, list, or textReturns the last non-blank value for all data types.
TEXTLISTTextReturns all of the values of a text-formatted line item, separated by a comma and a space.

Syntax example

TIMESUM(Revenue, -2, 0, SUM)

This formula sums the values for the Revenue line item from two periods before the current period through to the current period. This is inclusive of the start and end period.

Additional information

Default aggregation method

The default aggregation method used when you omit the Aggregation method argument varies based on the data type of the Line item to aggregate argument. If the data type of the Line item to aggregate argument is:

  • Number, the default behavior is to SUM.
  • Boolean, the default behavior is ANY.
  • Date, list, or text, the default behavior is FIRSTNONBLANK.

Optimize module performance with TIMESUM

The Start period and End period arguments for the TIMESUM function define the period to aggregate values over. The TIMESUM function only returns a single value, the aggregated value over this defined range of periods. As a result, it's best to use the TIMESUM in a line item without the Time dimension to optimize module performance.

Different data types for start and end period

The Start period and End period arguments can be used with number, date, or time period data type values. You can use different data types for each argument.

For example, you can specify a time period for the Start period argument, and 0 for the End period argument. This aggregates values between the specified time period and the current period and updates automatically as the current period changes.

Constraints

  • The Line item to aggregate argument must have Time as a dimension.
  • You must define a Current Period in Model Settings to use a number value for the Start period or End period arguments.
  • If you use a date value for either the Start period or End period argument, their positions are swapped. This means that the End period argument should be a date in the period when aggregation begins and Start period should a the date in the period when aggregation ends.

Calculation engine functionality differences

In Polaris:

  • You can only use a number for the Line item to aggregate argument.
  • If you only provide a value for the Start period argument and not the End period argument, the TIMESUM function aggregates all values from the Start period through to the end of the applicable time range.
  • When you use date values for the Start period and End period arguments, they behave as expected.

In the Classic Engine:

  • You can use a value with a data type of number, Boolean, date, list, or text for the Line item to aggregate argument.
  • If you only provide a value for the Start period argument and not the End period argument, the TIMESUM function returns the value from the Start period.
  • When you use date values for the Start period and End period arguments, the behavior is inverted. This means that the date for the Start period should be in the period where aggregation ends and the date for the End period should be in the period where aggregation begins.

Related Anaplan functions

MOVINGSUM

Examples

Start period and End period arguments

In this example, there are two modules. The first module contains the Time dimension on columns, and the Revenue line item on rows. The module is named Revenue 2021, which is referenced in later formulas.

The Current Period in this model, as defined in Model Settings > Time, is May 21.


Jan 21Feb 21Mar 21Apr 21May 21Jun 21Jul 21Aug 21Sep 21Oct 21Nov 21Dec 21
Revenue101,480130,156117,021122,556123,160143,432130,784134,415115,309117,279128,835108,029

The second module contains only line items, which each contain formulas. These formulas highlight how you can use the Start period and End period argument to specify the period of aggregation.

The reason the formulas are in a separate module is because you should use TIMESUM in a module without the Time dimension to optimize performance.

Revenue for all periods

TIMESUM(Revenue 2021.Revenue)

1,472,456

Revenue for 2 months ago

TIMESUM(Revenue 2021.Revenue, -2)

117,021

Revenue from 2 months ago to current period

TIMESUM(Revenue 2021.Revenue, -2, 0)

362,737

Revenue for duration of Spring campaign

TIMESUM(Revenue 2021.Revenue, TIME.'Jan 21', TIME.'Apr 21')

471,213

Example with only Line item to aggregate argument

The first formula, in the Revenue for all periods line item, uses only the Revenue line item for the Line item to aggregate argument. This means that the formula uses the default behavior for TIMESUM and sums all values in the module. This behavior can be useful when you use Weeks: General in Model Settings > Time, as this Calendar Type does not contain a summary of all periods.

Example with Start period argument

The second formula, in the Revenue for 2 months ago line item, uses the Revenue line item for the Line item to aggregate argument. The formula uses -2 for the Start period argument. This means that the formula returns only the value from the period two periods before the Current Period, May 21. In this case, this is the value 117,021 from Mar 21.

In Polaris, this formula would aggregate all values from and including Mar 21 until the last period in the applicable time range.

Example with Start period and End period argument

The third formula, in the Revenue from 2 months ago to current period line item, uses the Revenue line item for the Line item to aggregate argument. The formula uses -2 for the Start period argument and 0 for the End period argument. This means that the formula sums the values from two periods before the current period, through to the current period. In this case, this is 362,737, the sum of the values for Mar 21, Apr 21, and May 21.

Example with references to Time dimension

The fourth formula, in the Revenue for duration of Spring campaign line item, uses the Revenue line item for the Line item to aggregate argument. The formula uses references to the Time dimension for the Start period and End period arguments. Time.'Jan 21' and Time.'Apr 21' respectively. This means that the formula sums the values of the Revenue line item for and between these periods. As the formula uses references to the Time dimension, the values for the formula do not change as the current period in the Model Calendar changes.

Examples of different Aggregation methods

In this example, there are two modules. The first module contains the Time dimension on columns, and a number of line items on rows. The module is named Initiative KPIs 2021, which is referenced in later formulas.

The Current Period in this model, as defined in Model Settings > Time, is Dec 21.


Jan 21Feb 21Mar 21Apr 21May 21Jun 21Jul 21Aug 21Sep 21Oct 21Nov 21Dec 21
Revenue101,480130,156117,021122,556123,160143,432130,784134,415115,309117,279128,835108,029
Promotion Active?




New product release date

3/24/20214/30/2021

7/14/2021

/10/15/202111/1/2021
Product name

Apple pieCinnamon swirl

Sugar donut

Lemon meringue pieMille-feuille

The second module contains only line items, which each contain formulas. These formulas highlight the behavior of the different keywords for the Aggregation method argument. Each formula uses a Start period of -2 and End period of 0. This means that they apply to the values for Oct 21, Nov 21, and Dec 21.

The reason the formulas are in a separate module is because you should use TIMESUM in a module without the Time dimension to optimize performance.

Total revenue for last 3 months

TIMESUM(Initiative KPIS 2021.Revenue, -2, 0, SUM)

354,143

Average revenue for last 3 months

TIMESUM(Initiative KPIS 2021.Revenue, -2, 0, AVERAGE)

118,048

Lowest revenue for last 3 months

TIMESUM(Initiative KPIS 2021.Revenue, -2, 0, MIN)

108,029

Highest revenue for last 3 months

TIMESUM(Initiative KPIS 2021.Revenue, -2, 0, MAX)

128,835

Promotion active last 3 months?

TIMESUM(Initiative KPIS 2021.Promotion active?, -2, 0, ANY)

Promotion active for entirety of last 3 months?

TIMESUM(Initiative KPIS 2021.Promotion active?, -2, 0, ALL)


First product release date in last 3 months

TIMESUM(Initiative KPIS 2021.New product release date, -2, 0, FIRSTNONBLANK)

10/15/2021

Last product release in last 3 months

TIMESUM(Initiative KPIS 2021.New product release date, -2, 0, LASTNONBLANK)

11/1/2021

Names of products released in last 3 months

TIMESUM(Initiative KPIS 2021.Product name, -2, 0, TEXTLIST)

Lemon meringue pie, Mille-feuille

Numeric aggregation method examples

The first four line items contain formulas that demonstrate the behavior of the SUM, AVERAGE, MIN, and MAX keywords respectively. These aggregation methods can only be used with number-formatted values. For the values of Oct 21, Nov 21, and Dec 21, which are 117,279, 128,835, and 108,029, the formulas:

  • Sum each of the three values for a total of 354,143.
  • Return the mean average of the three values, 118,048.
  • Return the lowest value of the three, 108,029.
  • Return the highest value of the three, 128,835.

Boolean aggregation method examples

The fifth and sixth line items contain formulas that demonstrate the behavior of the ANY and ALL keywords respectively. These aggregation methods can only be used with Boolean-formatted values. For the values of Oct 21, Nov 21, and Dec 21, the formulas:

  • Check if any of the Boolean values are TRUE. As two of three are, it returns a value of TRUE.
  • Check if all of the Boolean values are TRUE. As only two of three are, it returns a value of FALSE.

FIRSTNONBLANK and LASTNONBLANK keyword examples

The seventh and eight line items contain formulas that demonstrate the behavior of the FIRSTNONBLANK and LASTNONBLANK keywords respectively. These aggregation methods can be used with number-, date-, list-, or text-formatted values. In this case, the line items contain dates that represent product release dates. For the values of Oct 21, Nov 21, and Dec 21, the formulas:

  • Return the first non-blank date-formatted value, 10/15/2021.
  • Return the last non-blank date-formatted value, 11/1/2021.

TEXTLIST keyword example

The ninth line item contains a formula that demonstrates the behavior of the TEXTLIST keyword. This aggregation method can only be used with text-formatted values. The formula concatenates the values of Oct 21, Nov 21, and Dec 21, separating each value with a comma. It returns Lemon meringue pie, Mille-feuille.

Disclaimer

We may update our documentation occasionally, but will only do so in a way that does not negatively affect the features and functionality of the Anaplan service.