Before You Begin

Learn how to create conditional formatting rules and apply the rules to date columns.

Background

You can identify trends and anomalies by defining conditional formatting on date columns in your dataset. This tutorial shows you how to define formatting rules comparing date columns to other date columns and comparing a date column with a measure column. The first conditional formatting rules find the orders shipped the same day as the order date. You also define a rule to show orders that ship more than three days from the order date.

To define conditional formatting rules, the date column must use a Date data type.

What Do You Need?

Create a Dataset

In this section, you create a dataset with the sample_order_lines2023.xlsx file.

  1. Sign in to Oracle Analytics.
  2. On your home page, click Create and click Dataset. In Create Dataset, click Drop data file here or click to browse. In File Upload, select sample_order_lines2023.xlsx and click Open.
  3. In Create Dataset Table from sample_order_lines2023.xlsx, click OK.
  4. Click the Order Date column. Click the sample_order_lines2023 tab. In Order Date properties, click DateTime in the Data Type row and select Date.
  5. In New Dataset, click Save Save icon. In Save Dataset As, enter sample_order_lines2023 and click OK


    Description of sample_order_lines2023_ds.png follows
    Description of the illustration sample_order_lines2023_ds.png
  6. Click the sample_order_lines2023 tab. In the Order Date column, click Options Column Options icon and select Calculate Duration.
  7. In Column Name, enter Days to Shipping. From the Time Difference list, select Since to calculate the number of days since the order was placed.
  8. From Relative to, select Ship Date. From the Calculate in list, select Days and click Add Step. Click Save.


    Description of days_to_ship.png follows
    Description of the illustration days_to_ship.png

Create a Workbook

In this section, you create a workbook with a table visualization.

  1. Click Create Workbook. Close the Insights panel.
  2. In the Data Data pane icon pane, hold down the Ctrl key, select:
    • Product Category
    • Product Sub Category
    • Order Date
    • Ship Date
    • Days to Shipping
    • Sales
    • Profit
  3. Right-click, select Pick Visualization, and then select Table Table icon.
  4. In the Grammar pane Rows, you can move the data elements to match the order of the selection from the Data pane.


    Description of prod_order_ship_date.png follows
    Description of the illustration prod_order_ship_date.png
  5. Click Save Save icon. In Save Workbook, enter a Name and click Save.

Create a Conditional Formatting Rule on Dates

In this section, you create a conditional formatting rule that looks for Ship Dates that are greater than Order Dates. When the rule is applied, the orders that shipped on the same date as the order date (same day shipping) are visible in the dataset.

  1. In the table visualization, hover over the visualization toolbar and click Manage Conditional Formatting Rules Conditional Formatting icon.
  2. In Conditional Formatting, enter Same Day Ship Date in Name.
  3. From Column, select Ship Date from Visualization Columns. In the rule row, keep Ship Date and keep greater than (>) as the operator. In the comparison field, select Order Date from Visualization Columns.
  4. Click Format Format icon. In Format, select a color to use or enter #fbc26a and click OK.


    Description of first_conditional_format.png follows
    Description of the illustration first_conditional_format.png
  5. In Conditional Formatting, click Save.


    The row in the table without formatting is the record with the order that was shipped the same date as the order date.

    Description of same_day_shipping.png follows
    Description of the illustration same_day_shipping.png

Define a Rule for a Date with Another Column

In this section, you define a rule to format the results of comparing a date column to a measure.

  1. In the table visualization, hover over the visualization toolbar and click Manage Conditional Formatting Rules Conditional Formatting icon. Click Add New Rule Add New Rule icon.
  2. In Conditional Formatting, enter Ship Date More than 3 days in Name.
  3. From Column, select Ship Date from Visualization Columns. In the rule row, select Days to Shipping and keep greater than (>) as the operator. In the comparison field, enter 3.
  4. Click Format Format icon. In Format, select a color to use or enter #f42d06 and click OK.


    Description of 2nd_conditional_format.png follows
    Description of the illustration 2nd_conditional_format.png
  5. In Conditional Formatting, click Save.


    Description of too_many_days.png follows
    Description of the illustration too_many_days.png

Learn More