Dynamic Time - detect your date format

When setting up the syntax for dynamic time, it is important to know the format of the individual levels of the Time hierarchy. In this article, the example is based on a four-level Year-Quarter-Month-Day (YQMD) hierarchy.

Name column format

One way to detect the format of the individual levels is simply to create a crosstab with all four levels.

The crosstab will show the format of the namecolumn values of the time dimension.

From this example, the Time definition may be defined as:

[<yyyy>].[Q<q>].[<Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec>].[<dd-MM-yyyy>]

Note: Month names may be localized. E.g., "May" can be "Maj" and "Oct" can be "Okt" in some languages. So make sure that you are using the correct names.

Key column format

Alternatively, the Time definition can be based on keycolumn values.

To determine the format of the keycolumn values, you should add a criteria from the YQMD dimension - on the lowest level, the Day level. Important: Pick a date where the day number is greater than 12 - to avoid confusing days with months.

Having applied the criteria, save the dashboard to your desktop (i.e., 'Save to file location').

Open the saved file in Notepad and look for the <globalCriteria ...> ... </globalCriteria> section. From the above example, it may look like this:

<globalCriteria immediaterefresh="true">{"Ver":3,"Criteria":
[{"Metadata":{"Database":"TARGIT Online Demodata","Cube":"Sales","Dimension":"YQMD",
"Hierarchy":"YQMD"},"Operator":"EQ","Kind":"Member","AutoRemoveMissingMembers":false,
"Value":[{"Key":"[Year].&amp;[2024].&amp;[20244].&amp;[202412].&amp;[20241224]",
"Name":["2024","Q4","Dec","24-12-2024"]}]}]}</globalCriteria>

From this, we can determine the format of the key values. Based on this example, the Time definition, using the keycolumn values, may look like this:

&[<yyyy>].&[<yyyyq>].&[<yyyyMM>].&[<yyyyMMdd>]