Red/Yellow/Green Banding in Tableau

Lets say your boss wants to be able to switch on or off some banding to show if your metric is in the red/yellow/green, AND sometimes “good” (green) is up, and sometimes it’s down.

So, this:
banding1

or this:
banding2

 

Sample File

Download the extracted workbook Sample here:
Banding-Sample

Clicking on either “Equity Ratio” or “Expense Ratio” will take you to a worksheet with the associated data.

What we’ll need

We’re going to make reference lines based on data elements in the database, so that the banding is automatically configured by our data source. So we need to define the following elements in our source data:

  1. positive_direction_flag (in our case either “up” or “down.” Up indicates green on top.
  2. Ceiling (in our case, a percentage, but could be a value)
  3. Floor

We have set ceiling and floor to be boundaries where the ceiling is always indicates green… like this:

When the positive_direction_flag is “up”

green band here
Ceiling Line 1
yellow band here
Floor Line 1
red band here

When the positive_direction_flag is “down”

red band here
Floor Line 2
yellow band here
Ceiling Line 2
green band here

 

To pull this off, we’re going to need the following elements in Tableau:

  1. A parameter to control showing/hiding banding
  2. Four calculated fields, one for each line we are making (shown above: Ceiling Line 1, Ceiling Line 2, Floor Line 1, Floor Line 2)
  3. Four reference lines, based on the values from the four calculated fields (but with the correct colors applied)

Create the Parameter

banding parameter

Create the Calculated Fields

Upper Line Green / Ceiling Line 1

I called Ceiling Line 1 in the breakout above “UpperLineGreen”  The formula is:

IIF([Show Banding]==’Show’,
IIF([Positive Direction Flag]==’up’, [Ceiling], null)
,null)

If the parameter equals “Show” AND positive_direction_flag (our database field) equals “Up” then return the value for ceiling (also from the database).

Bottom Line Red / Floor Line 1

IIF([Show Banding]==’Show’,
IIF([Positive Direction Flag]==’up’, [Floor], null)
,null)

Upper Line Red / Floor Line 2

IIF([Show Banding]==’Show’,
IIF([Positive Direction Flag]==’Down’, [Floor], null)
,null)

Note how the value is the one for floor even though it’s the line on the top!

Bottom Line Green / Ceiling Line 2

IIF([Show Banding]==’Show’,
IIF([Positive Direction Flag]==’Down’, [Ceiling], null)
,null)

Create the Reference Lines

First off, since we want to use our calculated fields for the reference lines, so they need to be added to the worksheet.  Then we create a reference line for each calculated field, with the color bands applied to the reference lines.

  1. Drag each of the four calculated fields to the white part of the Marks pane
    sumonmarks
  2. Notice that they say SUM… we have this data element on every associated date/value pair in our dataset, so summing the values up is no good.  Click the down arrow to the right of each field, change the measure to minimum.
    minonmarks
  3. Right click on the y-axis and click “Add Reference Line”
    1. Be sure to add the lines in the order presented below.
    2. Choose Line (you may think you need “Band” but you’d be wrong!)
    3. I used scope per cell.
    4. Value Min(UpperLineGreen) and Minimum.
    5. Fill above with green and below with yellow.
    6. Follow the screen captures below to add the corresponding reference lines.
Upper Reference Line Green

upperlinegreenreferenceline

Bottom Reference Line Red

bottomlineredreferenceline

Upper Reference Line Red

upperlineredreferenceline

Bottom Reference Line Green

bottomlinegreenreferenceline

One last trick

In this example I’m using a dual axis, one axis to display each ratio.  What you’ll see sometimes is “xx nulls” at the bottom of the screen:

nulls

To hide this from display:

  1. Right click on the y-axis
  2. Click Format…
  3. Make sure you are on the Pane tab
  4. Under Special Values (eg. NULL)
  5. Change Marks: to Hide (Connect Lines)
  6. Note that if you are using dual axis, you need to do this for each axis.

That should do it!

Leave a Reply

Your email address will not be published. Required fields are marked *