Search

TIMESUM

Aggregates between the From and To time periods to sum or average over selected periods. These can be absolute time periods or relative to the latest actual time period. It's useful when the result has no time dimension and refers to absolute time periods or time periods relative to the current period, as configured in Settings > Time > Model Calendar.

TIMESUM is appropriate when the result has no time dimension. It refers to absolute time periods or time periods relative to the current period.

MOVINGSUM requires the result to have a time dimension as it's used for moving sums and moving averages that vary over time.

Syntax

TIMESUM(x, [f] , [t], [a])

where:

  • x: The line item to add up over time.
  • f: The start period over which to aggregate. This period is included in the aggregation. (optional).
  • t: The end period over which to aggregate. This period is included in the aggregation. (optional).
  • a: Aggregation method (optional).

The TIMESUM function accepts a single mandatory parameter.

  • The only compulsory parameter is the line item being aggregated (x).

    • If both From and To parameters are omitted, all periods are added
    • If To is omitted, the period defined in the From parameter is returned
    • If the Aggregate parameter is omitted, default to SUM

Missing intermediate parameters are not permitted.

The following examples are valid:

TIMESUM(P&L.Revenue) 
  • Sums revenue over all time periods, excluding the brought forward period, if one exists. This is useful when you do not have a total of all time periods available, such as when the General Weeks timescale is used.
TIMESUM(P&L.Revenue,0)
  • Sums revenue for the current period only, including the brought forward period, if in range.
TIMESUM(P&L Revenue, Time.'Jul 14')
  • Sums revenue for July 2014 only, including the brought forward period, if in range..

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

TIMESUM(P&L.Revenue,,,AVERAGE)            
TIMESUM(P&L.Revenue,,0)            

The time periods can be absolute, such as:

Time.'Apr 14' 

(as per the time period label) or a number relative to the current period.

  • The Time.'Current Period' parameter can also be used for the defined current period.

    • 0 = current period

    • -1 = previous period

    • -2 = 2 periods ago

    • 1 = period after the current period.

    • The granularity of the time scale is taken from the line item. So, if the line item is in weeks, then -1 means the previous week.

Back to Top

Format

Input Format Output Format

x: Number, Boolean, date, List item, text

f: Number, TIME.'Period'

t: Number, TIME.'Period'

a: Keyword

Matches the data format used by argument x

Arguments

The function uses the following arguments:

  • x:
    • Number: Numeric line item, property, or expression
    • Boolean: Expression that equates to a Boolean result
    • Date: Hard-coded date, date, or line item.
    • List item: List
    • Text: Text-formatted line item, text constant, or general expression
  • f: (optional)
    • Number: Numeric line item, property, or expression
    • Time period: Number, TIME.'Period'
  • t: (optional)
    • Number: Numeric line item, property, or expression
    • Time period: Number, TIME.'Period'
  • a: (optional) Keywords: SUM, AVERAGE, MIN, MAX, ANY, ALL, FIRSTNONBLANK, LASTNONBLANK, TEXTLIST

Constraints

The function has the following constraints:

  • The only compulsory parameter is the line item you're aggregating.
  • If data type of source item changes and aggregate is inappropriate, then an error condition is triggered.
  • Result format can be Numeric, Boolean, date, list, or text but must be the same data type as the item being summed.

Excel equivalent

  • No Excel equivalent

Back to Top

Example

SUM for Revenue aggregations:

Last 3 months

TIMESUM(P&L Forecast.Revenue,-2,0)

Last 3 quarters

TIMESUM(P&L Forecast.Qtly Revenue,-2,0)

Last 3 days of Actuals

TIMESUM(P&L Forecast.Daily Revenue,-2,0)

Equivalent 3 months last year

TIMESUM(P&L Forecast.Revenue,-14,-12)

Next 3 months forecast

TIMESUM(P&L Forecast.Revenue,1,3)

Last 3 months using Time.'Current Period'

TIMESUM(P&L Forecast.Revenue,-2,Time.'Current Period')
If the Aggregate parameter is omitted, the function uses the default SUM.

AVERAGE for aggregations:

Average last year = TIMESUM(P&L Forecast.Revenue, Time,'Jan 13',Time.'Dec 13',AVERAGE)

TIMESUM can be particularly useful if you're building variance reports, that commonly contain summing columns for 'Current period' or 'Last 3 months':

TIMESUM(Sales,-2,0)        

In this example, Jun 14 is set as the Current Period for the model and TIMESUM is used to sum the Sales amounts for the Actual version for Apr 14=12,100, May 14=12,900, and Jun 14=0.

Back to Top

See also