Calendar Extract 
The Calendar Extract editor has been rebuilt with a new level-based interface. Hierarchy levels (years, quarters, months, weeks, etc.) are listed in a single view, where each can be enabled or disabled independently. Selecting a level opens its detailed configuration, which includes fields for pattern, start/end dates, time-to-date settings, and attributes. Configuration is preserved across levels — switching between them doesn't reset unsaved changes.
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
Select a language from the dropdown list. Most languages are listed as a single code (e.g.de, es, fr). English offers additional country-specific variants, en_UK and en_US. Date hierarchy levels
Level
The supported hierarchy levels are
- root (for a single constant root node)
- years (requires start and end)
- half-years
- quarters
- months
- weeks
- days
- hours
- minutes
- seconds
You can add more levels by clicking 
in the section header.
Start/End
Start and End define the range of values included for a given level.
- For the years level, Start and End set the absolute boundaries of the calendar — the first and last years for which nodes are generated. This is the only level where Start and End are required, since they define the overall calendar range.
- For other levels (quarters, months, etc.), Start and End are optional. Use them to restrict that level to a subset of its natural range — for example, setting quarters' Start to 1 and End to 2 generates nodes only for the first half of the year.
Both fields accept a literal value or a variable (e.g., ${First year}, ${Last year}), inserted via Insert variable in the toolbar. Using variables lets you define a calendar range that shifts automatically — for example, always covering the last 5 years relative to today — without hardcoding specific years into the extract.
Nodes that fall outside the years level's Start/End range produce an empty pattern result rather than an error; see Months offset for an example of this behavior.
Pattern
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.
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 levels
For each level that should generate time-to-date (TTD) nodes, the following settings can be defined:
- Level: the hierarchy level for which TTD nodes are generated. It must use a different pattern than the corresponding entry in the Date hierarchy levels table, so that the TTD nodes can be distinguished from the regular ones.
- Pattern: the generation pattern for the TTD node name.
- Mode: the TTD generation mode:
timeToDate,timeFromDate,timeToDateDeep,timeFromDateDeep,timeToDateCompact,timeFromDateCompact,storedTTDValues, orrolling. See Time-to-date hierarchy below for a description of each mode. - Reference level: the level that defines the start and end points for the aggregation of a TTD node, e.g.
yearsfor a year-to-date hierarchy. See Time-to-date hierarchy below. - Root node: the name of the root node under which the generated TTD hierarchy is placed. Required for the compact modes (
timeToDateCompact,timeFromDateCompact) and for a reference level ofroot, which creates the TTD nodes in a completely separate hierarchy.
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 |
|---|---|---|---|---|---|---|---|
| All 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 |
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 be 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:
| Date | 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 |
Empty cells indicate that result is empty; date falls outside the calendar's start/end range.
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", "2025 14", "2025 15", and "2025 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: Select a language from the dropdown list. Most languages are listed as a single code (e.g.
de,es,fr). English offers additional country-specific variants,en_UKanden_US. - 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 the US and "Lundi" in France.
Minimum of days in first week
Sets the minimum number of days required in the first week of the year, which determines how week 1 is calculated. Select a value from the dropdown list: Standard (use the language-specific default), 0, or 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 |
Time zone
Defines the time zone for date functions, taking daylight saving into consideration. For example, you can calculate the difference between two dates in locations with and without daylight saving (e.g., Eastern Time and India Standard Time) and the result will factor the difference in the number of days.
If no time zone is specified, then the system time zone is used.
Updated October 6, 2026