Loading

Combining a Date Field and a Time Field

Дата публикации: Apr 13, 2024
Задача
How to combine separate date and time fields to create one single datetime field.

Occasionally, data is stored in a way that is not ready for immediate analysis. For example, you may have a data source that contains a separate column for dates and a separate column for time. Because the date and time columns are separate, they are represented as two distinct fields in Tableau Desktop. Furthermore, when fields of the datetime data type do not have either a corresponding time or corresponding date, then a start time may be automatically assigned to the date data or a start date might be automatically assigned to the time data.
    Действия

    Primary Solution

    Step 1: Create a calculated field that combines the date and time columns

    1. In Tableau Desktop, connect to the data source. For this example, use the attached data source, Fruit Sales.xlsx.
    2. Select Analysis > Create Calculated Field.
    3. In the Create Calculated Field dialog box, do the following:
      1. In the Name text box, enter a name for the calculated field. For this example, use Date + Time.

      2. In the Formula text box, enter the following:

        DATEADD(

        'hour', DATEPART('hour', [Time Sold]), DATEADD(

        'minute', DATEPART('minute', [Time Sold]), DATEADD(

        'second', DATEPART('second', [Time Sold]), [Date Sold])))

      3. Click OK.

      The date and time columns are now combined into a single field and you can use the built-in date parts to drill down the datetime hierarchy.
       

    Step 2: Create the final view

    1. In the Data window, right-click the Date + Time field, and then drag it to the Rows shelf.
    2. In the Drop Field dialog box, click Date + Time (Discrete), and then click OK.

    Alternative Solution

    Step 1: Create a calculated field that combines the date and time fields.

    1. In Tableau Desktop, connect to the attached data source, Fruit Sales.xlsx.
    2. Select Analysis > Create Calculated Field.
    3. In the Calculated Field dialog box, do the following:
      1. In the Name text box, enter a name for the calculated field. For this example, use Date + Time.
      2. In the Formula text box, enter the following:

        DATETIME(STR([Date Sold]) + " "
        + STR(DATEPART('hour',[Time Sold]))+ ":"
        + STR(DATEPART('minute',[Time Sold]))+ ":"
        + STR(DATEPART('second',[Time Sold])))

    4. Click OK.

    Step 2: Create the final view

    1. In the Data window, right-click the Date + Time field, and then drag it to the Rows shelf.
    2. In the Drop Field dialog box, click Date + Time (Discrete), and then click OK.
    Номер статьи базы знаний

    001458074

    Вложения

    Fruit Sales.xlsx

    352 KB

     
    Загрузка
    Salesforce Help | Article