Customize Date and Time Fields in Multidimensional Data

For cube data sources, date dimensions are usually organized into hierarchies that contain levels such as year, quarter, and month. Also, some Fields in multidimensional data sources have time intelligence-enabled, making it possible to look at data levels in different ways, such as Months by Quarter, Months by Year, Weekends, etc. These levels are represented as the attributes of the hierarchy. Hierarchies and attributes are defined only when the cube is created, and you cannot modify them in Tableau. For instance, the Year dimension from an Oracle Essbase data source is shown below.

Dates in Relational Data Sources:

In relational data sources, dates and times are automatically placed in the Data pane’s Dimensions area and are then identified by the date or date-time icon. For instance, the Order Date and Ship Date dimensions from an Excel data source are shown below.

While placing a relational date on a shelf, the field name gets automatically modified to reflect the default date level. For instance, if the date field includes multiple years, the default level is a year. However, if the date field only contains data for one year but includes multiple months, the default level is a month.

Suppose you do not want Tableau to automatically select a data level and would rather have a date dimension be a continuous field. In that case, you can right-click the field in the Data pane and select the option Convert to Continuous. The dimension will then turn green in the Data pane; now, it will be continuous when you use that dimension in a view. You can easily revert by selecting Convert to Discrete from the field’s context menu in the Data pane. You can convert a field in the view to continuous while on a shelf by selecting Continuous on its context menu. The field on the shelf will turn green, but the field in the Data pane remains discrete.

Date Properties for a Data Source:

You can also set date properties for a data source. To do so, right-click on a data source in the Data pane and choose Date Properties:

The options available in the Date Properties dialog box are:

• Default calendar: It specifies which calendar system should be used: Standard Gregorian or ISO-8601 Week-Based. You can then freely switch between the calendar types with the dates in your view, but setting the default calendar will define how dates are used upon initially dragging them into your view.

• Week start: It specifies which day should be considered the first day of the week. You can also override the Week start value you set at the data source level by including a start_of_week value having certain date functions (DATEDIFF, DATENAME, DATEPART, and DATETRUNC).
• Fiscal year start: It specifies which month should be considered the first month of the fiscal year. For specifying whether a date dimension uses the standard calendar (Jan. 1 – Dec. 31) or the fiscal calendar, right-click on the field in the Data pane and select Default Properties > Calendar Type and select Standard Calendar or Fiscal Calendar.
• Date format – It specifies the default format for data dimensions, as displayed, for instance, in tooltips. For overriding the default date format for a date dimension, right-click on the field in the Data pane, select Default Properties > Date Format, and then select one of the available formats.

Custom Dates:

You may want to create a custom date if you are always using a date at a particular level or if you want to create calculations depending on a binned or truncated date.

You can also create a custom date by working in the Data pane or using the functions DATEPART and DATETRUNC in a calculated field.

The custom date you have created becomes a new field in the Data pane.

Step 1: Right-click a date field in the Data pane and select Create > Create Custom Date.

Step 2: In the Create Custom Date dialog box, type a name for the custom date, such as Date (Quarter, Year).

Step 3: From the Detail list, select the level you want to show the date.

Step 4: Specify the custom date as either discrete (select Date Parts) or continuous (select Date Value).

Step 5: When you are finished, click, OK.

Perfect Pivoting with Dates:

You can also pivot dates by placing different date levels on different worksheet shelves simultaneously. Place the date field on various shelves and then select the desired date level from the fields’ context menus.

For instance, the following line chart displays years as column headers and then color-encodes those marks by quarter.

You can also separate the marks by month and by quarter, as shown in the below image.

Continuous Dates:

You can also treat a date as a continuous quantity after placing the field on a shelf. You can do this by selecting one of the Continuous date options on the field’s context menu (lower list of date levels). Continuous dates draw a quantitative axis for the date values.

For example, the view below displays the sales as continuous order date and is color-encoded by category. As you can see, the Order Date field changes from blue to green after it is converted to a continuous quantity.

Treating dates as a continuous quantity is particularly useful when you use Gantt bars or want to see trends using line charts, as shown above.

By default, date dimensions are discrete fields for which Tableau automatically selects a date level when it is placed on a shelf. To make a date dimension continuous by default, right-click the data pane field and select Convert to Continuous. The field turns green and is automatically converted to a continuous field when you drag it to a shelf. To revert to discrete again, right-click the data pane field and select Convert to Discrete.

Format a date field in a view:

To format a date field, right-click the field and choose Format.

It will open the Format panel to the left of your view. Select the Dates field.

When you format dates, Tableau shows a list of available formats. In most cases, the last item on the list is Custom. You can specify a custom date using format symbols available in the Supported date format symbols table, either single or in combination.

Format date field in the Data pane:

To format a data pane date field, right-click the field and choose Default Properties > Date Format.