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.
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
The supported hierarchy levels are root (for a single constant root node), years, half-years, quarters, months, weeks, days, hours, minutes, and seconds.
Start and end are required for years.
Pattern sets the representation of the date. The following table lists the most important patterns:
| Pattern | Meaning | Example results for 31.12.2025 |
|---|---|---|
| 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: 2025; yy: 25 |
| Y | Week year | 2026 |
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.
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 also be set 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
1. Global months offset, starting year 2025:
| Pattern | Month offset 2 | Month offset -3 |
|---|---|---|
| MMM yyyy | Jan 2025 | Jan 2025 |
| OMMM Oyyyy | Mar 2025 | Oct 2024 |
| M.yy 'Q'Q '('OM Oyy 'Q'OQ')' | 1.15 Q1 (3.15 Q1) | 1.15 Q1 (10.14 Q4) |
2. Local months offset, start year 2025, end year 2026:
| Pattern MMMM yyyy | Pattern | Result |
|---|---|---|
| January 2025 | OMMM(1) Oyyyy(1) | Feb 2025 |
| January 2025 | OMMM(-12) Oyyyy(-12) | |
| January 2025 | OMMM(-12,false) Oyyyy(-12,false) | Jan 2024 |
| December 2026 | OMMM(1) Oyyyy(1) | |
| December 2026 | OMMM(-12) Oyyyy(-12) | Dec 2025 |
| December 2026 | OMMM(1,false) Oyyyy(1,false) | Jan 2027 |
| 2025 | Oyyyy(12) | 2026 |
Rolling periods
This option 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.
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 2025, an additional element "2025 ~" is generated before the first month element (e.g. Jan 2025). After the last month element (e.g. Dec 2025), the elements "2025 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. See Time-to-date hierarchy below.
- Special element pattern: optional attributes for special elements.
Week-specific settings
First day of the week: 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.2020 | CW 53 2019 | CW 00 2020 |
| 31.12.2024 | CW 01 2025 | CW 53 2024 |
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 |
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.
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.
Example of different modes
- Level: months
- ReferenceLevel: years
- Pattern: MM.yyyy 'TTD'
- Child nodes from node 04.2025 TTD
| timeToDate | timeFromDate | timeToDateDeep | timeFromDateDeep | storedTTDValues |
| 01.2025 | 04.2025 | 04.2025 | 04.2025 | 04.2025 |
| 02.2025 | 05.2025 | 03.2025 TTD | 05.2025 TTD | 03.2025 (*) |
| 03.2025 | 06.2025 | |||
| 04.2025 | 07.2025 | |||
| 08.2025 | ||||
| 09.2025 | ||||
| 10.2025 | ||||
| 11.2025 | ||||
| 12.2025 |
(*) consolidation factor -1
Examples for Year-To-Date hierarchy on months for the first half of a year
- Date hierarchy levels
| Level | Start | End | Pattern |
| root | 'All Months' | ||
| quarters | 1 | 2 | 'Qtr.'Q |
| months | MMM |
- 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 November 11, 2025