Skip to main content

Date & time fields

If you are working with fields containing date or timestamp information in your app, you can define a number of related attributes of a date, for example, year or week, and use them in your visualization.

Creating date fields in Data manager

Date fields are created automatically for all data fields recognized as a date or a timestamp when you use Add data with data profiling enabled to build your data model in Data manager, or when you click Load data in Data manager.

Note:

Date fields created in Data manager are automatically added to autoCalendar.

For more information, see Managing data in apps with Data manager.

If the date or timestamp field is not recognized automatically, you can adjust the input format in the Data manager table editor. You can also set the display format to use in visualizations.

For more information, see Changing field types.

Which date & time fields are automatically recognized ?

Date & timestamp fields will be recognized automatically based on your system locale settings. Additionally, the following formats are recognized:

  • M/D/YYYY h:mm
  • D/M/YYYY h:mm TT
  • M/D/YYYY
  • D/MM/YYYY
  • YYYYMMDD
  • YYYYMMDDhhmmss
  • YYYYMMDDhhmmss.fff
  • YYYYMMDDhhmmssK
  • YYYY-MM-DD
  • YYYY-MM-DDThh:mm:ss
  • YYYY-MM-DD-Thh:mm:ss.fff
  • YYYY-MM-DD-Thh:mm:ssK
Date & time formats
Format specifier Description
YYYY Year
M, MM Month
D, DD Day
hh Hour
mm Minute
ss Second
fff Millisecond
TT AM or PM
K Timezone
T Divider between date and time. T can not be replaced with another character.

Creating date & time fields in the data load script

If you use the data load editor to build your data model, you need to create a calendar template where you define which fields to derive in the data load script. The derived date & time fields will be generated when the script is run and data is reloaded.

For more information, see Derived fields.

Using date & time fields in your app

Date & time fields in visualizations

All date or timestamp fields in the assets panel Fields tab are marked with Date and time, and you can expand them to use the generated date & time fields. You can use them in visualizations, just like any other data field.

Date & time fields in dimensions

You can also use date & time fields when you create a dimension. The date & time fields are listed under the field that they have been generated from.

Date & time fields in expressions

You can use date & time fields in all expressions, for example when you create a measure. The date & time fields are named according to:

[field name].autoCalendar.[date & time field].

  • [field name] is the name of the data field that was used to generate date & time fields.
  • [date & time field] is the date & time field you want to use, for example, Year.

Example:  

Date.autoCalendar.Year

Date & time fields in calendar measures

Calendar measures use date & time fields created in autoCalendar. Each of these date & time fields is calculated by a set analysis expression that determines whether or not data falls within the time-to-date period, or if dates are within a defined relative position to the current date. These date & time fields are relative and return results based on the current date. You can use these fields independently of calendar measures.

Calendar field date & time fields are formatted as follows:

[field name].autoCalendar.[date & time field]={[value]}

  • [field name] is the name of the date field used to generate date & time fields.
  • [data & time field] is the name of the date & time field used, for example, InYTD.
  • [value] is the value for the date & time field’s set analysis expression and determines which dates are included.

Example:  

Date.autoCalendar.YearsAgo={1}

The following are the available date & time fields with sample values: