KNOWLEDGE BASE

Converting String Field with Year and Quarter to Date


Published: 12 Jun 2013
Last Modified Date: 12 Aug 2016

Question

How to convert a string field that contains year and quarter to a date field.

Environment

Tableau Desktop

Answer

Use one of the following options to convert the string YYYYQQ (e.g. 2014Q3) into a usable Date field in Tableau Desktop.

Option 1

Note: The first calculation assigns a month to each quarter so that it can be applied appropriately in the next calculation.
  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box, name the calculated field. In the example workbook, the calculated field is named First Month of Quarter.
  3. In the formula field, create a calculated field similar to the following:
    IF contains([Date String], "Q1") then "January"
    elseif contains([Date String], "Q2") then "April"
    elseif contains([Date String], "Q3") then "July"
    else "October" END
  4. Click OK.
  5. Select Analysis > Create Calculated Field.
  6. In the Calculated Field dialog box, name the calculated field. In the example workbook, the calculated field is named Date Field.
  7. In the formula field, create a calculated field similar to the following:
    date([First Month of Quarter] + " 1, " + left([Date String], 4)
  8. Click OK.

Option 2

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box, name the calculated field.
  3. In the formula field, create a calculated field similar to the following:
    IF INT(RIGHT([Date String], 1)) = 1
    THEN DATE("03/01/" + LEFT([Date String], 4)) 
    ELSEIF INT(RIGHT([Date String], 1)) = 2
    THEN DATE("06/01/" + LEFT([Date String], 4)) 
    ELSEIF INT(RIGHT([Date String], 1)) = 3
    THEN DATE("09/01/" + LEFT([Date String], 4)) 
    ELSEIF INT(RIGHT([Date String], 1)) = 4
    THEN DATE("12/01/" + LEFT([Date String], 4)) 
    END
  4. Click OK.

Option 3

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box, name the calculated field.
  3. In the formula field, create a calculated field similar to the following:
    dateparse("yyyyQQQ", [Date String])
  4. Click OK.
Did this article resolve the issue?