Before You Begin

This tutorial shows you how to calculate the time duration between two date attributes in Oracle Analytics and add a column with the duration (age) to your dataset.

Background

Duration is the number of years, months, days, minutes, and seconds between two dates in dataset records. This tutorial shows you how to calculate duration between order date and ship date.

What Do You Need?

Create a Dataset

In this section, you create a dataset to use for calculating duration.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create, and then click Dataset.
  3. In Create Dataset, click Drop data file here or click to browse, select the sample_order_lines2023.xlsx file, and then click Open.
  4. In Create Dataset Table from sample_order_lines2023.xlsx, click OK.

Calculate Duration

In this section, you create the duration calculation using Order Date and Ship Date.

  1. Click the sample_order_lines2023 tab.


    Description of order_ship_date_columns.png follows
    Description of the illustration order_ship_date_columns.png
  2. Select the Order Date column, click Options Options icon and select Calculate Duration.
  3. In Column Name, enter Duration to Shipping.
  4. From the Time Difference list, select Since to calculate how many days since the order was placed.
  5. From the Relative to list, select Ship Date.
  6. From the Calculate in list, select Days.


    Oracle Analytics adds a column named Duration to Shipping to the dataset.

    Description of order_ship_date_duration.png follows
    Description of the illustration order_ship_date_duration.png
  7. Click Add Step.
  8. Click Save Save icon. In Save Dataset As, enter order_to_shipping_duration, and then click OK.

Create a Workbook

In this section, you create a workbook with visualizations that show how you could use the Duration to Shipping data element.

  1. Click Create Workbook. Close the Auto Insights panel.
  2. In the Data Data pane icon pane, hold down the Ctrl key, select Product Sub Category, Quantity Ordered, Ship Mode, and Duration to Shipping. Drag the data elements to the canvas.
  3. Select Quantity Ordered in Values (X-Axis) and drag it to Values to switch places with Duration to Shipping.


    Oracle Analytics creates a scatter visualization of the data elements with the Duration to Shipping on the X-Axis.

    Description of days_qnty_psc.png follows
    Description of the illustration days_qnty_psc.png
  4. In the Data Data pane icon pane, hold down the Ctrl key, select Product Sub Category, Quantity Ordered, Order Priority, and the Duration to Shipping. Drag the data elements to the canvas and release next to the existing visualization.
  5. Select Quantity Ordered in Values (X-Axis) and drag it to Values to switch places with Duration to Shipping.


    Description of num_days_ship_prodsubcat.png follows
    Description of the illustration num_days_ship_prodsubcat.png
  6. Click Save Save icon. In Save Workbook, enter Shipping Days and click Save.
  7. Click Go back.

Modify the Calculate Duration Step

  1. On the Home page, hover over the sample_order_lines_order_duration dataset, click Actions Actions icon and select Open.
  2. In the Preparation Script Preparation script pane icon pane, hover over Calculate Duration to Shipping and click Edit Edit preparation step icon.
  3. In Calculate Duration, select Hours from the Calculate in list. Click OK.


    Description of calc_duration_hrs.png follows
    Description of the illustration calc_duration_hrs.png
  4. Click Save Save icon and click Go back Go back icon.
  5. On the Home page, hover over the Shipping Days workbook, click Actions Actions icon and select Open.
  6. Click Edit Edit icon. Close the Auto Insights panel.
  7. Click Refresh Refresh dataset icon to update the Duration to Shipping calculation to show the change to hours.


    Description of wbk_duration_hrs.png follows
    Description of the illustration wbk_duration_hrs.png

Learn More