Calendar Extract

This extract creates a tree that can consist of years, half-years, quarters, months, weeks, days, hours, minutes, and / or seconds. Connection to a source system is not required, allowing time dimensions to be easily created in Jedox Integrator and imported later into a Jedox database.

Settings

Language	The language should be set as a combination of a default language code and a default country code. They can be found at http://www.loc.gov/standards/iso639-2/php/English_list.php and https://en.wikipedia.org/wiki/ISO_3166-2. The pattern is <language>_<country>, but it is also possible to specify only <language>. For example, English can be set as en_US, en_UK, or en_AU, and German can be set as de_DE, de_CH, or de_AT. If <country> or both codes are not defined, the appropriate system default value will be used for the undefined code.
Date hierarchy levels	Level: the supported hierarchy levels are root (for a single constant root node), years, half-years, quarters, months, weeks, days, hours, minutes, and seconds. Start: start of the levels (required for years). End: end of the levels (required for years). Pattern: pattern for the representation as a text.
Patterns Overview	The following table lists the most important patterns: Pattern Meaning Example results for 31.12.2014 d Days in month 31 D Days in year 365 E Day in week EEE: Wed; EEEE: Wednesday M Month in year MM: 12; MMM: Dec; MMMM: December q Half year 2 Q Quarters in year 4 v Weeks in year (corresponding to y) 53 w Weeks in year (corresponding to Y) 1 W Weeks in month 5 y Year yyyy: 2014; yy: 14 Y Week year 2015 The pattern symbols q, Q, and v are Jedox Integrator-specific enhancements of the standard SimpleDateFormat, which can be found under http://download.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html.
Fixed text elements in calendar	If the output should contain fixed text elements, then these can be defined with a single quotation mark ('text'). For the root level, the output can only consist of fixed text elements. Example: If you want to create a Year Dimension with half-years, quarters, and months, then the settings will look like this: Date hierarchy level Start End Pattern root     'Year' months     MMM quarters     'Qtr.'Q halfyears     'Half year'q Result: level1 level2 level3 level4 Year Half year 1 Qtr. 1 Jan Year Half year 1 Qtr. 1 Feb Year Half year 1 Qtr. 1 Mar Year Half year 1 Qtr. 2 Apr Year Half year 1 Qtr. 2 May Year Half year 1 Qtr. 2 Jun Year Half year 2 Qtr. 3 Jul Year Half year 2 Qtr. 3 Aug Year Half year 2 Qtr. 3 Sep Year Half year 2 Qtr. 4 Oct Year Half year 2 Qtr. 4 Nov Year Half year 2 Qtr. 4 Dec
Week-specific setting	First day of the week The first day of the week. The numerical values of option in older versions are replaced with text representations. If not set, the language-specific value is used, e.g. "Sunday" in US and "Lundi" in France. Minimum of days in first week The minimum of days required in the first week of the year. For example, if the first week is defined as one that contains the first day of the first month of a year, it is 1. If it must be a full week, it is 7. If not set, the language-specific value is used. Possible values are standard, 0 (zero), and 4. Weeks can be generated in two different ways: With pattern letters "v" and "y": in this case, the weeks are ascending in one calendar year, starting from week 0 or 1. All days under the week belong to the same calendar year. With pattern letters "w" and "Y": in this case, the weeks may start with 53 or 54 and end with 1 in one calendar year. Some of the days under the week may belong to a different calendar year. Note that you should not use the combinations v/Y and w/y. Example with week level: Pattern 'CW' w YYYY 'CW' v yyyy 01.01.2010 CW 53 2009 CW 00 2010 31.12.2014 CW 01 2015 CW 53 2014
Months offset	For the calendar, a month offset can be specified, e.g. to handle fiscal years that differ from a calendar year (non-calendar fiscal year). In this case, the additional pattern symbol "O" has to be set as prefix to the symbols M, Q, q, and y. The month offset is not available in combination with the weeks level. It shouldn't been used in combination with days. Instead of this global calendar setting, a month offset can be set also locally inside of the pattern with syntax "O(<offset)". This allows the use of different offsets in one calendar, e.g. for previous and next month. If the resulting calendar entity is outside of the time range of the calendar (as defined by the start and end parameters of the years level), the result of the pattern is empty; i.e., the node or attribute value is not created. For locally defined patterns, this logic can be turned off with the syntax "O(<offset>,false)". Examples: Global months offset, starting year 2015: Pattern Month offset 2 Month offset -3 MMM yyyy Jan 2015 Jan 2015 OMMM Oyyyy Mar 2015 Oct 2014 M.yy 'Q'Q '('OM Oyy 'Q'OQ')' 1.15 Q1 (3.15 Q1) 1.15 Q1 (10.14 Q4) Local months offset, start year 2015, end year 2016: Pattern MMMM yyyy Pattern Result January 2015 OMMM(1) Oyyyy(1) Feb 2015 January 2015 OMMM(-12) Oyyyy(-12)   January 2015 OMMM(-12,false) Oyyyy(-12,false) Jan 2014 December 2016 OMMM(1) Oyyyy(1)   December 2016 OMMM(-12) Oyyyy(-12) Dec 2015 December 2016 OMMM(1,false) Oyyyy(1,false) Jan 2017 2015 Oyyyy(12) 2016
Rolling periods	This setting is required for time-to-date hierarchies of type "rolling".
Special elements	To add an element for an opening period or to add month elements for fiscal years with more than 12 periods, you can define special elements for each level. A pattern has to be defined for each individual element, separated by a semicolon (;). With the prefix "^" the special element is generated before all other elements, otherwise it will be generated after all other elements. Examples: Level years, pattern: '~' An additional element "~" is generated after all other years Level months, pattern: ^yyyy '~'; yyyy 13; yyyy 14; yyyy 15; yyyy 16 For the current year 2015, an additional element "2015 ~" is generated before the first month element (e.g. Jan 2015). After the last month element (e.g. Dec 2015), the elements "2015 13", "2015 14", "2015 15", and "2015 16" are generated.
Attributes	For each level, the following attributes can be defined: Level: the hierarchy level on which the attribute value will be set Attribute: name of the attribute Pattern: the generation pattern Here you can also use the "months offset" syntax, which allows you to create attribute values to specify, for example, the previous month for the current element. Furthermore, you can use the patterns of the DateFormat function (cd, cM, cQ), allowing you to generate an attribute for storing a "timestamp". Examples: Element Name: 2023-02; Pattern: Oyyyy(-1)-OMM(-1) -> will generate "2023-01" as value for the attribute (the previous month). Element Name: 2023-02; Pattern: cM -> will generate "1478" as value for the attribute (the number of months elapsed since January 1900). Language Time-to-date pattern: optional attributes for the nodes generated for time-to-date hierarchies Special element pattern: optional attributes for special elements

Time-to-date hierarchy

The Calendar extract allows the generation of hierarchical nodes for a time-to-date (TTD) analysis, which is a general term for year-to-date, month-to-date, etc.

The levels for which additional TTD nodes should be generated have to be defined in the time-to-date levels table in Integrator. They must have a different pattern than the "normal" date hierarchy levels so that the nodes can be distinguished. In most cases, one TTD level should be sufficient.

There are a number of generation nodes available:

timeToDate: sums up all nodes starting from 1 up to the current node. This is the most-used version for a year-to-date hierarchy.

timeFromDate: sums up all nodes starting from the current node up to the last node. This can be used in year-to-go scenarios.

timeToDateDeep: similar to timeToDate node, but uses a recursive definition of the consolidations, which leads to a lower number of total nodes but a deeper hierarchy. This generation pattern was used in ETL up to version 5.1 for TTD hierarchies.

timeFromDateDeep: similar to timeFromDate node, but uses a recursive definition of the consolidations, which leads to a lower number of total nodes but a deeper hierarchy.

timeToDateCompact: generates two separate hierarchies: one for standard nodes and one for time-to-date nodes in a compact form. The TTD nodes are created under the specified root element and sum up all nodes starting from 1 up to the current node.

timeFromDateCompact: generates two separate hierarchies: one for standard nodes and one for time-to-date nodes in a compact form. The TTD nodes are created under the specified root element and sum up all nodes starting from the current node up to the last node.

storedTTDValues: used when the base node itself contains aggregated time-to-date values. The generated consolidated nodes then represent aggregated values by subtracting the previous node from the current node with a consolidation factor of -1.

rolling: generates a separate hierarchy for rolling periods under the specified root element. The global parameter "Rolling periods" defines the number of preceding month nodes, e.g. 12 for a 12-month rolling hierarchy.

Reference level: each TTD hierarchy has its own reference level that defines the start and end points for the aggregation of a TTD node. For example, for a year-to-date hierarchy on several years, the reference level should be "year". For the reference level "root", it is possible to create the TTD nodes in a completely separated hierarchy by specifying the name of its root node.

Note that in ETL up to version 5.1, the time-to-date reference "toNext" specified the next higher date hierarchy level and the time-to-date reference "toRoot" specified the reference level "root".

Example of different modes

• Level: months
• ReferenceLevel: years
• Pattern: MM.yyyy 'TTD'
• Child nodes from node 04.2015 TTD

timeToDate	timeFromDate	timeToDateDeep	timeFromDateDeep	storedTTDValues
01.2015	04.2015	04.2015	04.2015	04.2015
02.2015	05.2015	03.2015 TTD	05.2015 TTD	03.2015 (*)
03.2015	06.2015
04.2015	07.2015
	08.2015
	09.2015
	10.2015
	11.2015
	12.2015

(*) consolidation factor -1

Examples for Year-To-Date hierarchy on months for the first half of a year

1. Date hierarchy levels

Level	Start	End	Pattern
root			'All Months'
quarters	1	2	'Qtr.'Q
months			MMM

2. Time-to-date levels

Level	Pattern	Mode	Reference level	Root node
months	MMM 'acc'	timeToDateDeep	root	All YTD

This results in a year-to-date hierarchy for months, with additional nodes for quarters.

level1	level2	level3	level4	level5	level6	level7	level8
JAll YTD	Jun acc	May acc	Apr acc	Mar acc	Feb acc	Jan acc	Jan
All YTD	Jun acc	May acc	Apr acc	Mar acc	Feb acc	Feb
All YTD	Jun acc	May acc	Apr acc	Mar acc	Mar
All YTD	Jun acc	May acc	Apr acc	Apr
All YTD	Jun acc	May acc	May
All YTD	Jun acc	Jun
All Months	Qtr. 1	Jan
All Months	Qtr. 1	Feb
All Months	Qtr. 1	Mar
All Months	Qtr. 2	Apr
All Months	Qtr. 2	May
All Months	Qtr. 2	Jun

Updated August 1, 2024