Interaction with time ranges and superset

Calculation functions have specific ways of interacting with time ranges and supersets. This page describes these interactions and provides guidance on how to address certain scenarios.

In general, for functions with a time period argument:

• If the argument is a value (rather than a reference to a line item), the period name will only be available for selection if included in the superset. And you will not be able to remove the period from the superset if it is referenced by a calculation: Anaplan protects the relationship.
• If the argument is a reference to a source line item formatted as Time Period, then the source line item could contain any period from the superset, or blank. In the case of a mismatch between this superset and the time range of a result line item or another source line item, then periods not found in the superset are ignored. For example, the formula Source[Lookup: TimePeriod], where TimePeriod is a period-formatted line item, would return zero where TimePeriod contains a period not included in the time range of Source.
• If the argument is a reference to a source line item formatted as Quarter or Half-Year, there is a further dependency on the superset. When Quarter Totals or Half-Year Totals are removed from the superset, line items using the relevant granularity will revert to default granularity (months or weeks depending on calendar type). This would cause the values in the source line item to change, and a function using that argument may return a different result. This effect is not reversed by restoring an aggregation level to the superset—the format will not change back automatically.

Certain function-specific interactions are described below.

For all the following, if a TimePeriod argument is a line item formatted as Quarter or Half-Year, and Quarter Totals or Half-Year Totals are removed from the superset, then its format will revert to default and its value will change. The function using the argument could then return a different result. This would not be reversed if the optional aggregation level was restored to the superset.

• x[LOOKUP: TimePeriod]
• ISANCESTOR(TimePeriod, TimePeriod)
• INPERIOD(date,TimePeriod)
• END(TimePeriod)
• DAYS(TimePeriod)

Functions that return a time period are also affected. For example, PERIOD(Date): if the result is a line item with a Quarter or Half-Year format, and Quarter Totals or Half-Year Totals are removed from the superset, then its format will revert to default and the function will return a different period.

Example: Source line item[SELECT: TIME.Period name]

Period name includes detail time periods (months, weeks) and year totals. Also, the optional aggregation levels Quarter Totals and Half-Year Totals, for example [SELECT: TIME.'Q1 FY18'].

Period name must be in the superset.

It’s not possible to submit a formula with a reference to a period outside the superset.

You will not be able to remove the period from the superset if it is referenced by this function: Anaplan protects the relationship.

If the time range of Source line item does not include Period name, it returns zero (or default value for other data types). This includes the optional aggregation levels, for example Source line item[SELECT: TIME.'Q1 FY18'] would always return zero if the time range of Source line item does not have Quarter Totals enabled.

Examples:

• Source line item [SELECT: TIME.YTD]
• Source line item [SELECT: TIME.YTG]
• Source line item [SELECT: TIME.All Periods]

The period name must be in the model calendar and the corresponding aggregation level must also be enabled.

You will not be able to remove the aggregation level from the model calendar if it is referenced by this function. And, it’s not possible to submit a formula with a reference to an aggregation level not enabled in the model calendar: Anaplan protects the relationship.

If the time range of Source line item does not include the aggregation level, it returns zero (or default value for other data types). For example [SELECT: TIME.'All Periods'] would always return zero if the time range of the source does not have Total of All Periods enabled.

If Time > Model Calendar > Current Period is not specified or empty, then [SELECT: TIME.YTD] and [SELECT: TIME.YTG] will return zero.

Example: Source line item [SELECT: TIME.Current Period]

Choose a period from the model calendar and enable it for the Current Period variable.

If Current Period is not specified (empty), then [SELECT: TIME.Current Period] will return zero.

You can remove the Current Period from the model calendar if it is referenced by this function. Anaplan doesn't protect the relationship — the system accepts the function whether or not optional aggregation levels are available.

If the time range of Source line item does not include the Current Period, this returns zero (or default value for other data types).

Example: FINDITEM(time, “Period name”)

Period name could be a detail time period, for example months or week, or a year total, or one of the optional aggregation levels Quarter Totals and Half-Year Totals.

If Period name is in the superset, this will return the corresponding time period. If it is not, this will return blank: Anaplan doesn't protect the relationship.

The result line item must be formatted as Time Period. If there is a mismatch of granularity between Period name and the time format, the function will return blank. For example, FINDITEM(Time, “Jan 18”) will always return blank in a cell formatted with a Time Period of Year.

If an optional aggregation level is removed from the superset then any line item with a period format at that granularity will revert to default granularity (months or weeks depending on Model Calendar > Calendar Type). It will not change back if the aggregation level is restored to the superset.

For example, if the result line item is Time Period formatted as Quarter, and period name is Q1 FY18, then the function will return a quarter. But if Quarter Totals is removed from the superset, the format will change to the default granularity, and the function will then return blank. Restoring Quarter Totals to the superset will not cause the line item format to change back to Quarter.

When FINDITEM is used in a sub-expression, its result is unconstrained by the format of the result line item—it will return a period if it is present in the superset, otherwise blank. When not in a sub-expression, the function result is influenced by the format of the result item

If there is a mismatch of granularities it will return blank (this is not a display effect).

Examples:

FINDITEM(time, “All Periods”)

FINDITEM(time, “YTD”)

FINDITEM(time, “YTG”)

The aggregation level for the period name should be enabled in the model calendar.

This will return the corresponding time period, for example YTD. If it is not, this will return blank: Anaplan doesn't protect this relationship.

There is no time period format that will display the result as a time period, as YTD etc. aren’t present in any available level.

But expected results are returned when the function is in a sub-expression. For example, NAME(FINDITEM(Time, "All Periods")) returns Total of All Periods, if enabled in the model calendar.

Example: FINDITEM(time, “Current Period”)

Current Period should be enabled in the model calendar: a period from model calendar should be chosen for Current Period.

If Current Period is enabled in the model calendar, this function will return the time period set as the current period, for example Mar 18. If Current Period is not specified (empty), then this function will return blank.

You are able to remove the Current Period from the model calendar if it is referenced by this function: Anaplan doesn't protect the relationship.

Usually, in a sub-expression, this function will return the time period. However, in some cases this function will return blank. For example, where NAME(FINDITEM(Time, "Current Period")) returns Mar 18, the formula FINDITEM(Time, "Current Period") in a Month formatted line item will return blank.

Function returns a time period within the time range of the result line item (the time range used for line item dimensionality).

The result line item must have time as a dimension.

When not in a sub-expression, the result line item must be formatted as Time Period, and the aggregation level chosen will determine the result. For example, this could return Jan 18 in a month formatted cell, but if the line item format were changed to Quarter it would return Q1 FY18.

For example, when used in a line item formatted with a Time Period of Quarter, ITEM will return a quarter. But if Quarter Totals is removed from the superset, the format will change to the default granularity, and the function will then return a month or week. Restoring Quarter Totals to the superset will not cause the line item format to change back to Quarter.

Function results depend on whether the time range of the source line item has optional aggregation levels enabled.

The result line item must have time as a dimension.

The functions will return quarter or half year values if the time range of the source line item has these aggregation levels enabled. Otherwise, this returns zero: Anaplan doesn't protect this relationship.

Dependent on time range of result line item.

The result line item must have time as a dimension.

These functions can be used if the relevant optional aggregation level is enabled in the time range of the result line item.

Otherwise, the system will not accept the function for the line item. If a line item uses one of these functions, you will not be able to remove the relevant aggregation level from the time range used by the line item: Anaplan protects this relationship.

Results are dependent on superset.

The function will return a time period at a granularity determined by the line item format. For example, month, quarter, or year.

For example, when used in a line item formatted as Quarter, PARENT will return a quarter. But if Quarter Totals are removed from the superset, the format will change to the default granularity, and the function will then return a month (or week). Restoring quarters to the superset will not cause the line item format to change back to Quarter.

Where unconstrained by result line item format, the function returns next available parent from the superset.

For example, PARENT(month) will return a quarter if Quarter Totals are enabled in the superset. If they are not, it will return a half year, if Half Year Totals are in the superset. And if Half Year Totals are not enabled, it will return a year. This effect is reversible — adding (or removing) an aggregation level in the superset is reversed if the level is removed (or re-added).

If period is a line item formatted as Quarter or Half Year, and Quarter Totals or Half Year Totals are removed from the superset, then its format will revert to default granularity, and its value will change. This could change the result returned by PARENT. It will not change back if the aggregation level is restored to the superset.