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 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
FINDITEM(time, “All Periods”)
|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|
|Time Range applied to the Source line item (src)||This function will not be rejected if optional aggregation levels are not available.|
|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.|
(When used in sub-expression)
|Superset||Picks next available parent|
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.