Skip to main content

Convert a Field to a Date Field in Tableau

Convert a Field to a Date Field

Each database stores Date fields in slightly different ways. Tableau does its best in interpreting the date fields, but sometimes a field will be imported to Tableau as a text string or numerical field. If this happens, there are a few steps we can take to resolve the issue.
Verifying Date Fields
The date field might appear as a string when connecting to the Data Source...
Or as a string in the Dimensions Pane.
When dates are interpreted as strings, you will lose all of the features and conveniences of working with date fields, such as drilling down, using date calculations, and switching between Continuous and Discrete measures.
If your dates aren't interpreted correctly, take the following steps in order:
  1. Change the data type of the field
  2. Create a calculation using the DATEPARSE function
  3. Create a calculation using the DATE function

Change the data type of the field

The first step in resolving a date field interpretation issue is to make sure the data type is set to Date or Date & Time.
  1. In the Data Source or Dimensions pane, click the data type icon and change the data type to Date or Date & Time
  2. Inspect the data in the view or Data Source pane. If you see many Null values, return the data type to String and proceed to Create a calculation using the DATEPARSE function to correct the issue.
    Null Values showing in the Data Source screen.

Create a calculation using the DATEPARSE function

Dates are stored in a nearly infinite array of formats. Some date fields have years before months, others separate the parts of the date with periods, and still others use a combination of formats. When Tableau cannot interpret a date field, it might be because the particular format cannot be translated.
The DATEPARSE function lets you clearly define which parts of your field are which parts of a date. In essence, you are creating a map that Tableau can use to translate the string into a date field. This map is referred to as the format.
Note: This function is available through the following connectors: non-legacy Excel and text file connections, Amazon EMR Hadoop Hive, Cloudera Hadoop, Google Sheets, Hortonworks Hadoop Hive, MapR Hadoop Hive, MySQL, Oracle, PostgreSQL, and Tableau extracts. Some formats may not be available for all connections.

Creating the DATEPARSE calculation

  1. Inspect the current format of your date field. Note where months, years, hours, and days are displayed in your field. You will need this information for the DATEPARSE function.
    A. Day of month
    B. Month
    C. Year
    D. Hour, minute, second, millisecond, period
  2. Right click your date field in the Dimensions pane and select Create > Calculated Field.
  3. In the dialog, write the DATEPARSE function. The DATEPARSE function has two parts: the format and the string. The string is field you wish to convert, which must be a string data type.
    A. Format
    B. String
    The format is the guide Tableau will use to interpret the string as a date. Each part of a date or time string has a matching symbol, as seen in the table below. The format must exactly duplicate the way that the date is displayed. For example, if a single year code ("Y") is used when the string has a 2-digit year code ("97"), the calculation might return null values.
    Note: The exception to the format exactly duplicating the displayed string is that long form parts of dates ("September") can be formatted with four symbols ("September" = "MMMM").
    The format must include all spaces, hyphens, and other non-alphanumeric symbols in order to interpret the string correctly.

    Date Field Symbols

    Date PartSymbolExample StringExample Format
    YearY2016, 97, 2YYYY,YYY,YY,Y
    EraGAD, Anno DominiGGGG
    MonthM9, 09, Sep, SeptemberM, MM, MMM, MMMM
    Week of year (1-52)w8,27w, ww
    Day of Monthd1, 15d, dd
    Day of Year (1-365)D23, 143DDD,DDD
    PeriodaAM, am, PMaa, aaaa
    Hour (1-12), Hour (0-24)h, H1, 16, 03h, HH, hh
    Minutem8,59m, mm
    Second, Milliseconds, A24, 2, 34532ss, s, AAAAA

    Note: Some date formats are not supported by all databases and file types.
  4. Check your format against the string displayed in Tableau. If the symbols and formatting is correct, select OK to create the new calculated field. The calculated field will act as a date field in your view.

Hyper extracts

For .hyper extract data sources, the symbols are defined by the Unicode Consortium. For more information.

Locale considerations

The DATEPARSE function relies on the locale specified by your computer settings to interpret and then display the strings that you want to convert. More specifically, the locale will affect whether a certain format can be recognized. This means that if a format is not supported by the locale, then you might see a null value or no value returned. For example, suppose you have the following string in your data:
12Sep2016:9:8:8.6546
The values returned from the DATEPARSE function for this string are different based on locale. In the English locale you will get a certain value, but for the Japanese locale you will get no value. In this case, no value is returned because the Japanese locale does not recognize “Sep.”
Datetime value
English localeJapanese locale
#9/12/2016 9:08:09 AM#-
As with all dates, after you have used the DATEPARSE function to convert a string to a datetime type, by default Tableau will display the datetime value in the default format of your locale. If the locale changes, the result of the DATEPARSE function might display your new datetime value in a different format.
Create a calculation using the DATE function
If the DATEPARSE function is not available for the data that you're working with, or the field you are trying to convert is a number data type, you can use the DATE function instead.
The DATE function converts a number, string or date expression to a date type. When you create a calculation that uses the DATE function, Tableau creates a new field in your Tableau data source that allows you to interact with your date data as a date. To successfully produce date values from a number, string, or date expression using the DATE function, Tableau needs to be able to interpret the components of the string into date parts. After the components of the date are identified, Tableau uses the computer locale to determine the default format of the date.
For example, suppose the table you're working with contains a column of date data that is called "Original Date." The "Original Date" column is a string type.
Original Date
03Jan2017
05Jan2017
07Mar2017
19Mar2017
30Apr2017
In this case, you can create a calculated field called "New Date" that uses an expression in a DATE function to convert the string values in the "Original Date" field into date values.
For this example, the date expression is comprised of the LEFT function to isolate the day component, the MID function to isolate the month component, and the RIGHT function to isolate the year component.
DATE (LEFT([Original Date], 2) + "/" + MID([Original Date],3,3) + "/" + RIGHT([Original Date],4))
The "New Date" calculation produces the following column:
New Date
1/3/2017
1/5/2017
3/7/2017
3/19/2017
4/30/2017
In this example, the new date values are based on an English locale and default formatting.
Labels:

Comments

Post a Comment

Popular Posts

Add Space between bars in Tableau chart

Add Space between bars in Tableau chart Scenario: Tableau defaults to no spacing between the panes in a view. How do I get some spacing between groups of bars in my charts? It can be achieved through the steps below: Add subtotals (Analysis->Totals->Add All Subtotals) Right click on the measure pill on the Rows shelf and change the default SUM() aggregations to MIN() To edit the color legend – double click on the Total color, this takes you to a color dialog, select the WHITE color, click OK Right click on the word Total in the X axis and select Format In the Format window, click the Header tab and blank out the Total label field The added space looks like below:
1 comment
Read more

Adding “Apply” Button to A Filter Menu In Tableau

Adding An “Apply” Changes Button To A Filter Menu In Tableau Sometimes when a user is changing the options they want on a filter in Tableau, the chart updates as they change each option. This might not be the best user experience if they are changing many options and don’t want the dashboard to redraw the view until they have completed their selection. It is very simple to fix this – Tableau includes an “apply” changes option, which when enabled, means the dashboard won’t redraw to reflect the new filter choices until the user presses the “apply” button. Simply click the drop down arrow on the filter menu > go to “customize”, then click “show apply button”.  
1 comment
Read more

Error "Cannot mix aggregate and non-aggregate arguments with this function"

Error "Cannot mix aggregate and non-aggregate arguments with this function" When Creating a Calculated Field When creating a calculation, the following error might occur:  "Cannot mix aggregate and non-aggregate arguments with this function." The issue can be resolved through one of the below options: Modify the calculation so that all fields are either aggregate or non-aggregate. Each option can result in different values (please reference the additional information section for specific examples). Option 1 (Aggregate Then Divide) Wrap all fields in an aggregation. Sample: [Profit] / SUM ([Sales]) -> SUM ([Profit]) / SUM ([Sales]) Option 2 ( Divide Then Aggregate) Remove aggregations from all of the fields. Sample: [Profit] / SUM ([Sales]) ->[Profit] / [Sales]   Option 3 ( Condition Then Aggregate) Move the aggregation so all fields are aggregated. For example, the calculation: IF [Row ID] = 1 THEN SUM( [Sales] ) END  could...
Post a Comment
Read more

Removing "Abc" Placeholder Text in Tableau Measures column

Removing "Abc" Placeholder Text in Tableau Measures column Quick and easy fix for this issue could be: Use Polygon mark type On the Marks card in the dropdown menu, select Polygon Resize the last column to make it smaller Navigate to Format > Borders In the left-hand Format Border Pane, for Column Divider, For Pane, select None from the dropdown menu Navigate to Format > Shading In the left-hand Format Border Shading, for Row Banding, move the slider to the desired level of row banding
2 comments
Read more

Tableau Bar Chart Rounded corners

Tableau Bar Chart Rounded corners Follow the steps in the video below to create rounded corners for the bar chart:
Post a Comment
Read more

Using Special Characters in Tableau URL Parameters

Using Special Characters in Tableau URL Parameters When we try to use special characters in URL parameters, the URL parameter might not do anything, or an error might occur. The issue can be resolve by:  Use one of the following workarounds: Replace the special character with the URL encoding sequence for backslash (\) (%5c) followed by the URL encoding sequence for the special character. The backslash is needed to escape the special character. For example, the URL encoding sequence for backslash and comma (\,) is %5c%2c.  In the data source, separate comma-delimited field values into separate columns that can be filtered independently. In Tableau Desktop, use a calculated field to replace the special characters, such as commas or spaces, with hyphens (-). Cause The browser cannot parse the special characters used in the URL. Additional Information The error varies depending on the browser and special character being used. Networ...
1 comment
Read more

Cannot mix aggregate and non-aggregate arguments with this function

Cannot mix aggregate and non-aggregate arguments with this function Issue When creating a calculation, one of the following errors might occur:    "Cannot mix aggregate and non-aggregate arguments with this function." (Option 1,2,3 or 4 can be used). " All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources." (Option 1 or 3 can be used). "Argument to sum (an aggregate function) is already an aggregation, and cannot be further aggregated ."  (Option 2, 3 or 4 can be used). Environment Tableau Desktop Resolution Each option can result in different values (please reference the attached workbook in the right-hand pane and additional information section for specific examples). Option 1 (Aggregate All Fields) Wrap all fields in an aggregation. Sample: [Profit] / SUM ([Sales]) -> SUM ([Profit]) / SUM ([Sales])   Option 2 ( De-aggregate All Fields) Remove aggregations from all of ...
Post a Comment
Read more

Set a Date Filter Default to Max Date in Tableau

Set a Date Filter Default to Max Date in Tableau Some of us might have not noticed the setting a   filter to default to the Max date. There is a quick and simple option to achieve this in Tableau filters. Follow the steps below: 1. Create a date filter with the date dimension. 3. Drag the date dimension to filters pane. 4. Edit the date filter to Select from the list and select the max date available. 2.    Now, y ou have the option  'Filter to latest date value when workbook is opened', check this box and click on OK.
1 comment
Read more

Tableau Extract Update Error "Timeout Error: IPC_NamedPipe::Select(WaitForMultipleObjects)

Tableau Extract Update Error "Timeout Error: IPC_NamedPipe::Select(WaitForMultipleObjects) When trying to update the Data Source or w hen attempting to connect to a data source, or create and/or refresh extracts with Tableau Desktop, the following error message occurs:  IPC_NamedPipe::Select(WaitForMultipleObjects): Timeout. Cause Anti-virus software is blocking Tableau processes from running, or the Logs folder contained corrupted information.  Resolution Option 1: If it is easy w ork with IT helpdesk/support to add below Tableau folders and processes to exclusions : Tableau.exe hyperd.exe hyperdstarter.exe *.hyper extension (if AntiVirus has an option for extension exclusions) C:\Users\<username>\Documents\My Tableau Repository C:\Users\<username>\AppData\Local\Temp\TableauTemp C:\Users\<username>\AppData\Local\Tableau C:\Program Files\Tableau Make sure that child folders are included in the exclusions. AntiVirus prog...
Post a Comment
Read more