Search

Time Ranges and Calculation Functions

Generally, calculation functions support mixed time ranges: the result line item and any line item arguments can have different time ranges. These are normal or non time series functions.

Time series functions must use the same time range for the line item holding the function, and any line items that provide arguments. This includes all line items referenced in sub-expressions. There are nine time series functions that must conform to this rule.

Normal functions

Normal functions can fetch data from a line item with a different time range applied: cells that display values from outside the time range are displayed as blank. Some normal functions, shown below, take a time period as an argument. The time period that can be used depends on the function.

Time Periods available to non time series functions

Function Available Periods from Time Range Notes
SELECT: TIME.period name
FINDITEM(time, “period name”)
Superset Including detail time periods (e.g. months, weeks) and year totals. Also the optional aggregation levels quarter and half year (e.g. [SELECT: TIME.'Q1 FY18']). The period name will only be available for selection if included in the superset. You will not be able to remove the period from the superset if it is referenced by a calculation.
SELECT: TIME.All Periods
SELECT: TIME.YTD
SELECT: TIME.YTG
FINDITEM(time, “All Periods”)
FINDITEM(time, “YTD”)
FINDITEM(time, “YTG”)
Model calendar The period name will only be available for selection if included in the model calendar. You will not be able to remove the period from the model calendar if it is referenced by a calculation.
ITEM(time) Time Range applied to the Result line item  
QUARTERVALUE(src)
HALFYEARVALUE(src)
Time Range applied to the Source line item (src) This function will not be rejected if optional aggregation levels are not available.
QUARTERTODATE()
HALFYEARTODATE()
Time Range applied to the Result line item This function will be rejected if optional aggregation levels are not available.
ISANCESTOR() Superset This function will be rejected if optional aggregation levels are not available.
PARENT(Timeperiod)
(When used in sub-expression)
Superset Picks next available parent

PARENT function

If you enable Quarter Totals or Half-Year Totals for an individual time range, it becomes available to the whole model when that time range is used as a dimension. This functionality can cause the PARENT function to change behavior when it is used with a line item formatted as Time Period. The PARENT function uses the model granularity to determine the immediate ancestor of a time-formatted line item. Adding a time range with an intermediate granularity will break this relationship.

Error Message using SUM and LOOKUP

The functions SUM and LOOKUP use a source value and at least one relational value to generate a result value. This could cause an invalid formula error message.

In the example:

result = source[SUM:relation]

The relation correlates information from the source time range with the result time range. The formula is evaluated as invalid if the relation used in the function does not entirely cover any overlap between the source and result.

In the diagram below, if the relation covers:

  • entirely and only the green area (FY16), the formula will be evaluated and a result returned.
  • the green area and either of the red areas (FY15 & FY17 or FY16 & FY19) and (FY16), an Invalid formula dialog is shown.
  • just a red area (FY15) or (FY17 & FY18), the error will not be displayed, but the function will return 0.