CLOSINGPERIOD

Returns the last element with the specified characteristics.

Syntax

ClosingPeriod(Dimension, Element, DistanceFromRoot)

ClosingPeriod returns the last element with these characteristics:

  • It is at the same XMLA level in the hierarchy as the element you specify in the Element parameter.
  • It shares at least one ancestor with the specified element.

The function returns an empty string if the parameters are incorrect or there is no element with the required characteristics.

If the dimension contains multiple hierarchies (elements with multiple parents), use HCLOSINGPERIOD.

Examples

In the examples, a hierarchy of all years, years, quarters and months is used. In this hierarchy, the All Years element is the root element (Level 1).

The years 2005, 2006, 2007, and so on, are at Level 2.

The quarters of each year, 2005_Q1/Q2/Q3/Q4, 2006_Q1/Q2/Q3/Q4 etc. are at Level 3.

The months within the quarters, 2005_Q1_01/02/03, 2006_Q2_04/05/06 etc. are at Level 4.

Example 1

This example returns 2017_Q4_12:

ClosingPeriod('Period', '2005_Q4_12', 0)

The element returned by ClosingPeriod is calculated from the root element (All Years - Level 1).

The last element in the hierarchy that is at the same native level as 2005_Q4_12 is 2017_Q4_12. It has the same ancestor (All Years).

Example 2

This example returns 2005_Q4_12:

ClosingPeriod('Period', '2005_Q4_12', 1)

The element returned by ClosingPeriod is calculated from Level 2 (one below the native root level). This is 2005.

There is no other element at the same native level as 2005_Q4_12 which has 2005 as its ancestor.

Example 3

This example returns 2017_Q1_03:

ClosingPeriod('Period', '2017_Q1_01', 2)

The shared parent is 2017_Q1. The last element at the same native level as 2017_Q1_01 is 2017_Q1_03. It is the same distance (2 native levels down) from the root.