TIMESUM

Examples

Start period and End period arguments

In this example, there are two modules. The first module contains theTimedimension on columns, and theRevenueline item on rows. The module is namedRevenue 2021, which is referenced in later formulas.

TheCurrent Periodin this model, as defined inModel Settings > Time, isMay 21.

The second module contains only line items, which each contain formulas. These formulas highlight how you can use theStart periodandEnd periodargument 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 theTimedimension to optimize performance.

Example with only Line item to aggregate argument

The first formula, in theRevenue for all periodsline item, uses only theRevenueline item for theLine item to aggregateargument. 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 useWeeks: GeneralinModel Settings > Time, as thisCalendar Typedoes not contain a summary of all periods.

Example with Start period argument

The second formula, in theRevenue for 2 months agoline item, uses theRevenueline item for theLine item to aggregateargument. The formula uses-2for theStart periodargument. This means that the formula returns only the value from the period two periods before theCurrent 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 theRevenue from 2 months ago to current periodline item, uses theRevenueline item for theLine item to aggregateargument. The formula uses-2for theStart periodargument and0for theEnd periodargument. 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 theRevenue for duration of Spring campaignline item, uses theRevenueline item for theLine item to aggregateargument. The formula uses references to theTimedimension for theStart periodandEnd periodarguments.Time.'Jan 21'andTime.'Apr 21'respectively. This means that the formula sums the values of theRevenueline item for and between these periods. As the formula uses references to theTimedimension, the values for the formula do not change as the current period in theModel Calendarchanges.

Examples of different Aggregation methods

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

TheCurrent Periodin this model, as defined inModel Settings > Time, is Dec 21.

The second module contains only line items, which each contain formulas. These formulas highlight the behavior of the different keywords for theAggregation methodargument. Each formula uses aStart periodof-2andEnd periodof0. This means that they apply to the values forOct 21,Nov 21, andDec 21.

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

Numeric aggregation method examples

前四行项目包含德公式monstrate 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.
• 返回最后一个非空date-formatted价值,11/1/2021.

TEXTLIST keyword example

第九行项目包含一个公式,恶魔trates 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 returnsLemon meringue pie, Mille-feuille.