Conditional Formatting and Traffic Lighting with Multiple Conditions and Formats

Version 5

    Step by step instructions about how to create a worksheet for quarterly reviews with traffic lighting using conditional formatting with multiple independent conditions and multiple formats in the same column using Tableau.

     

    See the attached files.

     

    In our use case we had a process in place for gathering metrics from department heads.  We needed a way to display them in a column in Tableau and color them as Red, Green or Yellow (RGY).  The conditions that drove the RGY colors were different on each row in the column.  We also needed to format the cells as $, % or integers within the same column as appropriate.Excel does this with conditional formats and number formats.  Here’s how to do it in Tableau.


    Bob Lingard

    ExcelTableau Comparison.png

    Tableau Conditional Formatting and Traffic Lighting

     

    Step by step instructions about how to create a worksheet for quarterly reviews with custom formats and traffic lighting using conditional formatting with multiple independent conditions in Tableau

     

    Bob Lingard

     

     

    In our use case we had a process in place for gathering metrics from department heads.  We needed a way to display them in a column in Tableau and color them as Red, Green or Yellow (RGY).  The conditions that drove the RGY colors were different on each row in the column.  We also needed to format the cells as $, % or integers as appropriate. Excel does this with conditional formats and number formats.  Here’s how to do it in Tableau.

     

    1. In Excel, prepare the data with an optional duplicate set of columns. If the numeric option is chosen, it will be used for calculations.
      1. Formatted columns (Critical):  Format the original set of columns exactly as needed for presentation purposes, with dollar and percentage symbols, commas and number of decimal points; and where the cell contains the result of subtracting percentages, format as a number with one decimal point followed by “ pts”.
      2. Numeric columns:  Leave in numeric format for calculations.  To easily identify the columns that are numeric and differentiate them from the formatted columns, add “ Numeric” to the end of every column label.
    2. In Tableau, create a new worksheet
    3. Connect to the data that has the extra set of numeric columns
    4. Drag Description to the rows shelf
    5. Create Column:
      1. Drag Number of Records to the columns shelf, creating a “pill”
      2. Repeat, creating a second “pill” on the columns shelf, to the right of the first pill
      3. In right pill, change Measure to MIN(Number of Records)
      4. Repeat for left pill
      5. In right pill, select dual axis
      6. Verify that the columns merge (Critical).
        1. If not, repeat and QC the above steps until the columns merge.
      7. Right click the bottom axis
      8. Look at the marks card (located under the filters shelf card) and note that the first column is highlighted.  Now right click the top axis and note that the second column is highlighted in the marks card. This means that the bottom axis is column 1, the first of the merged pair of columns, and the top axis is column 2, the second of the merged pair of columns.
      9. Now right click the bottom axis again.
        1. Select Edit Axis
        2. Select Fixed and specify 0-1
        3. Blank-out the Title
        4. Select Tick Marks tab and select None for Major and Minor Tick Marks
        5. Click Apply
        6. Close the Edit Axis dialogue box by clicking X in upper right
        7. Click outside the bottom axis and verify it is blank
      10. Right click top axis
    6. Repeat same steps, except enter the column heading as the Title, e.g., “Q Actual vs Q Plan”
      1. Note that you’re creating the column header when you type into the Title box.  In your Tableau visualization the column headers won’t be column names or their aliases, they’ll be the axis headers that you type in this step.
    7. Add values to the bottom axis:  Drag the value to the label shelf.  E.g.,
      1. On the marks card, select column 1 (remember, the bottom axis is column 1, the first of the merged pair of columns)
      2. Drag the formatted column Q Actual vs Q Plan to the label shelf
    8. Fill the cells with color
      1. Remove Measure Names from the color shelf
        1. Select the bottom axis (already selected in step 6) and remove Measure Names
        2. Select the top axis and remove Measure Names
        3. Select the bottom axis and continue
      2. From the drop down shapes menu, select Bars
      3. Verify that the values are properly visible.  If not, QC.
    9. Determine the color of the cell for Traffic Lighting – Red, Green or Yellow (RGY)
      1. Two Approaches (Critical): 
        1. Pre-calculated flags with the character R, G or Y
        2. Numeric calculation such as If-then statements within Tableau (Appendix)
    10. Color the cells with the appropriate condition.
      1. On the marks card, select the bottom axis
      2. Locate the appropriate calculated item and drag to the color shelf
      3. Set standard colors
        1. Red 216 31 40
        2. Green 109 179 68
        3. Yellow 255 225 79
      4. Verify the colors are correct by comparing with the input template
      5. If not, QC
    11. Remove object from Top axis
      1. On the Marks shelf, select the top axis
      2. Click on the bottom of the color icon to reveal the transparency slider
      3. Slide all the way to zero
      4. Verify the object is no longer visible.
    12. Repeat, starting with Step 3.
    13. Note the color settings are lost when new columns are added and must be reset when finished
    14. Color any non-Traffic lit columns: The bar reduces the font slightly, so any non-traffic lit columns must also be colored in order to have the appearance of a consistently sized font.
      1. On the marks card, select the first of the pair of merged columns
      2. Left-click the color icon
        1. Set the neutral color as 242 242 242
        2. Remove the border
          1. Select None for Border
    15. Set the font
      1. Reformat worksheet to font=14
      2. Resize the column width
      3. Resize the height of the axis titles by dragging the column axis title line
      4. Ensure that the titles wrap
      5. Increase depth of the workspace to ensure there is adequate area for the rows by dragging the row line at the bottom of the Description column
    16. Fine tune the white grid
      1. Horizontal
        1. On the Marks shelf, click the Size icon
        2. Nudge the slider to the right until you have your desired fineness of line width
      2. Vertical
        1. On the Columns shelf, click the down arrow of the second pill
        2. Select format
        3. On the left hand vertical format bar, select the Borders icon
        4. Under Column Divider, select Pane
        5. Select the line width that most closely matches your desired fineness of line (e.g., the second finest)
        6. Select white

     
     

    Appendices I-III – Conditional Formulas

     

    Appendix I: Conditional Formulas - RGY Q Actual vs Plan

     

    If [Description]="Bookings" then

        If [Q Actual Numeric]/[Q Plan Numeric]>=1 then "G"

        elseif [Q Actual Numeric]/[Q Plan Numeric]>=.95 then "Y"

        elseif [Q Actual Numeric]/[Q Plan Numeric]<.95 then "R"

        else "NA"

        end

     

    elseif [Description]="Revenue" then

        If [Q Actual Numeric]/[Q Plan Numeric]>=1 then "G"

        elseif [Q Actual Numeric]/[Q Plan Numeric]>=.95 then "Y"

        elseif [Q Actual Numeric]/[Q Plan Numeric]<.95 then "R"

        else "NA"

        end

     

    elseif [Description]="Op Exp" then

        If [Q Actual Numeric]/[Q Plan Numeric]<=1 then "G"

        elseif [Q Actual Numeric]/[Q Plan Numeric]<=1.02 then "Y"

        elseif [Q Actual Numeric]/[Q Plan Numeric]>1.02 then "R"

        else "NA"

        end

     

    elseif[Q Actual Numeric]>=[Q Plan Numeric] then "G"

    elseif [Q Actual Numeric]-[Q Plan Numeric]>=-0.005 then "Y"

    elseif[Q Actual Numeric]-[Q Plan Numeric]<-0.005 then "R"

    else "NA"

      

    end

     

    Appendix II: Conditional Formulas - RGY Q Actual vs Commit

     

    If [Description]="Op Exp" then

    if [Q Actual Numeric]/[Q Forecast Numeric]>=0.98 AND [Q Actual Numeric]/[Q Forecast Numeric]<=1 then "G"

    elseif [Q Actual Numeric]/[Q Forecast Numeric]>=.95 AND [Q Actual Numeric]/[Q Forecast Numeric]<.98 then "Y"

    elseif [Q Actual Numeric]/[Q Forecast Numeric]<0.95 then "R"

    elseif [Q Actual Numeric]/[Q Forecast Numeric]>1 then "R"

    else "NA"

    end

     

    elseif [Q Actual Numeric]/[Q Forecast Numeric]>=0.98 AND [Q Actual Numeric]/[Q Forecast Numeric]<=1.02 then "G"

    elseif [Q Actual Numeric]/[Q Forecast Numeric]>1.02 AND [Q Actual Numeric]/[Q Forecast Numeric]<=1.05 then "Y"

    elseif [Q Actual Numeric]/[Q Forecast Numeric]<0.98 AND [Q Actual Numeric]/[Q Forecast Numeric]>=0.95 then "Y"

    elseif [Q Actual Numeric]/[Q Forecast Numeric]>1.05 then "R"

    elseif [Q Actual Numeric]/[Q Forecast Numeric]<0.95 then "R"

    else "NA"

    end

     

    Appendix III: Conditional Formulas - RGY FY vs Plan

     

    If[Y Forecast Numeric]/[Y Plan Numeric]>=1 then"G"

    elseif [Y Forecast Numeric]/[Y Plan Numeric]>=0.95 then "Y"

    elseif [Y Forecast Numeric]/[Y Plan Numeric]<0.95 then "R"

    else "NA"

    end