Before You Begin

In this 15-minute tutorial, you learn how to create rules that apply conditional formatting to your data in Oracle Analytics visualizations.

Background

In Oracle Analytics, you can define rules on measures such as sales, profit, and other measures to visualize specific events with conditional formatting. You can create rules that format a measure compared to a specific target, a measure compared to a percentage of a value, a measure compared with the results of an expression, and a measure in a set of thresholds such as with a high, average, and low sales in a stoplight format.

Your rules define the visual formatting options such as bar, line, and cell colors, font and font styles, and background colors. When the condition exists that is defined in a rule, the visualization changes to show the formatting in your rule.

Your rules can apply to all visualizations in your workbook or to individual visualizations.

What Do You Need?

Create a Dataset and Workbook

In this section, you create a dataset from the spreadsheet file that you download to your computer.

  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. In File Upload, select sample_orders_examples.xlsx, and then click Open.
  4. In Create Dataset Table from sample_orders_examples.xlsx, click OK.


    Description of dataset_table.png follows
    Description of the illustration dataset_table.png
  5. Click Save. In Save Dataset As, enter Sample Orders in Name, and then click OK.
  6. Click Create Workbook.


    Description of workbook_data_panel.png follows
    Description of the illustration workbook_data_panel.png

Create a Visualization

In this section, you create a visualization in the Sample Orders dataset.

  1. Close the Insights panel.
  2. In the Data panel under Sample Orders, hold down the Ctrl key, select Sales, Profit, and Product Sub Category, and then drag them to the canvas.


    Oracle Analytics automatically selects a scatter visualization.

    Description of sales_profit_scatter_viz.png follows
    Description of the illustration sales_profit_scatter_viz.png
  3. Select Product Sub Category in Category (Points) and drag it to Color.
  4. Select Profit in Values (X-axis) and drag it to Values (Y-axis) to change places with Sales.


    Description of scatter_color.png follows
    Description of the illustration scatter_color.png

Define a Conditional Formatting Rule

In this section, you define rules that format the data points that show the least profitable and the most profitable product sub categories.

  1. In the visualization, click Conditional Formatting Conditional Formatting icon.
  2. In Conditional Formatting, enter Least Profitable Products in Name.
  3. From the Measure list, select Profit. From the operator list, click the less than symbol (<). In Target, enter 0 (zero).
  4. Click Format Format icon. In Format, select a color to use, and then click OK. Click Save.


    This example uses bright pink (#f700d8) as the color.

    Rubber bands, scissors, rulers, and trimmers aren't profitable items making the paperless office seem like a reality. Surprisingly, bookcases and tables aren't profitable.

    Description of no_profit_rubber_bands.png follows
    Description of the illustration no_profit_rubber_bands.png

Add a Condition

In this section, you add a condition to the least profitable products rule to format the most profitable products.

  1. In the visualization, right-click, select Conditional Formatting, and then select Edit Rules.
  2. In Conditional Formatting, click Add Condition Add Condition icon. Leave Profit as the measure. From the operator list, select greater than (>), and in Target, enter 100000.
  3. Click Format Format icon, and select a color for the condition, and then click OK. Click Save.


    This example uses bright green (#60ff00) as the color.

    The colors of the product sub categories change for the data points that meet the conditional formatting rules.

    Description of most_profit.png follows
    Description of the illustration most_profit.png
  4. Click Save. In Save Workbook, enter Sample Orders WB, and then click Save.

Change the Visualization Type

In this section, you change the visualization type to show the impact of the conditional formatting rules.

  1. In the visualization, click Menu Menu icon, select Edit, and then select Duplicate Visualization.
  2. Click Change Visualization Type Change Visualization Type icon, and then select Pivot.
  3. In the visualization, click Menu Menu icon, select Sort by, and then select Custom.
  4. In Sort Order, select Profit from the Sort By list, and then select Low to High. Click OK.


    Description of sales_profit_pivot.png follows
    Description of the illustration sales_profit_pivot.png

Create a Threshold Conditional Formatting Rule

In this section, you create a conditional formatting rule that shows high, medium, and low thresholds in your sales data. This rule uses a three color format to represent thresholds, and is frequently called stoplight or traffic light formatting.

  1. In each visualization, click Menu, and select Delete Visualization.
  2. In the Data panel, hold down the Ctrl key, select Sales and Product Sub Category, and then drag them to the canvas.


    Description of sales_by_prod_sub_cat.png follows
    Description of the illustration sales_by_prod_sub_cat.png
  3. Right-click, select Conditional Formatting, and then select Add Rule.
  4. In Conditional Formatting, enter Sales Milestones in Name. From the Measure list, select Sales. Click 3 Steps Threshold Preset 3 Color Preset icon.
  5. In the less than (<) operator, enter 100000. Click Format Format icon, enter #e31c3d to make the lowest sales threshold red, and then click OK.
  6. In the value in the next less than (<) operator, enter 500000. Click Format Format icon, and then enter #f9c642 to color the mid-range sales yellow, and then click OK.


    Oracle Analytics inserts 500000 in greater than or equal to threshold.

  7. Next to the greater than or equal to (≥) threshold, click Format Format icon, enter #2e8540 to color the product sub categories with the highest sales green, click OK, and then click Save.
  8. In the visualization, click Menu Menu icon, select Sort by, and then select Sales, and then select Low to High.


    Description of sales_prod_sub_stoplight.png follows
    Description of the illustration sales_prod_sub_stoplight.png

Learn More