How to Set Conditional Formatting for Visualizations

This article describes how to go from a visualization showing all data series in the same color...

...to a visualization where data series are conditionally formatted. In the example below, all series with values greater than or equal to 50% are green, series with values greater than or equal 40% and less than 50% are orange, and series with values less than 40% are red.

Requirements

It is assumed that you have a table with a single column that contains the values you want to visualize and format conditionally.  If the table has multiple columns, please see If the Table Has Multiple Columns section below.

Method

Create the Conditional Palette

1. In the toolbar, go to Calculation  > Custom Code.

2. Click on the page to create the calculation.

3. With the calculation selected, click the Edit Code button in the R Code section on the Data tab in the object inspector and then copy and paste the following R code:

#select data table 
value = table.Q4.Consider

#specify condition and colors
#you can use color names (e.g. Green) or HEX Code (e.g. #008000)
colors <- ifelse(!is.na(value), 
     ifelse(value >= 50, "Green", 
          ifelse(value >= 40 & value < 50, "Orange", 
               ifelse(value < 40, "Red", "Black"))), "NA")

# Reformat as a character vector
palette <- as.character(colors)

4. Click into the code next to the equals sign on the second line and replace the reference to "table.Q4.Consider" with the name of the table. If the table is on the same page as the calculation, then you can just click the blue tab with the table name as text hovering above the table to insert the reference. Otherwise, you can select the table and find its name on the General tab in the object inspector.

Edit the "ifelse" part of the code to reflect the values and colors you want to use. The colors here should be expressed as HEX codes (e.g., #FFFFFF for white), but you can also use some common terms for predefined colors.

As a handy tip, if you're unsure how to write the "ifelse" part, then ChatGPT can help. Copy the whole "color" section into ChatGPT along with the following prompt:

Update R code to return (insert your color) for values above (insert values), (insert color) above values of (insert value) and use (color) for all other values.

See an example here.

When done, you should have a vector (single column) of colors that looks something like this:

Create the Visualization

1. In the toolbar, go to Visualization  > Bar > Bar.

2. On the Data tab in the object inspector, select the table using the Data dropdown in the Data Source section.

3. On the Chart tab in the object inspector, check Multiple colors within a single series in the Data Series section.

4. Select Custom Palette (R output) using the Color palette dropdown.

5. Select the calculation with the vector of colors using the Custom palette dropdown. It will be called "palette" because that's the name we give it on the last line of the R code.

If the Table Has Multiple Columns

In this scenario, there are a couple of minor changes to the process above, but all other steps remain the same:

For step 3, when you insert the code, the reference to the table should include a reference also to the table column. As an example, if the table has three columns, with the columns labeled "Brand A," "Brand," and "Brand X," and the goal is to show the values for "Brand B" in the visualization, then change the code on the first line to include the column reference like this:

value = table.Q4.Consider[, "Brand B"]

The other difference is that when creating the visualization, it should specifically reference the correct column. To do so, select the visualization and on the Data tab in the object inspector, type or paste the column name into the Columns to show field in the Column Manipulations section.

Next

How to Apply a Gradient Palette to a Bar, Column, or Pyramid Visualization

How to Customize the Color of a Single Category in a Bar, Column, or Pyramid Visualization