Business Intelligence Tools

Spotfire:  Using Document Properties to Reverse a Color Scheme Based on Desired Outcome

Have you ever wanted to reverse a Spotfire visualization color theme based on whether an outcome is desired or not?

For example, positive production almost always equates to a desired outcome while one might want to see CAPEX decrease.  If switching between the two options in a visualization via a Document Property, we might want to indicated a desired outcome as green and an undesired outcome as red rather than simply designating positive numbers as green and negative numbers as red.

Today, we will show you how to use a document property to create a switch, which will then alter the color scheme to denote whether an outcome is desired or undesired rather than positive or negative.

Recently, I have been working on a Waterfall Chart that compares forecasts and can accept any value.  Often, the user will bring in a data table that has both production and CAPEX in the data table.  Naturally, the Waterfall Chart will show positive changes as green and negative as red.  However, we want our CAPEX to go down from forecast to forecast.  We generally associate green with good results and red with bad but in the case of CAPEX, green denotes an increase, a poor outcome.  So, I want to give the user the option to switch whether green represents a positive or negative.

To do so involved two different components:  creating a Property Control switch and using that Property Control in a visualization’s if statement.  Check out the steps below to make your own.

Create Control Property Switch

First, we want to create a Property Control drop-down switch that only has two options:  Desired Increase and Desired Decrease.

  1. Right-click in the text area where you want your switch
  2. Click Edit HTML.
  3. Click the Insert Property Control icon .
  4. Select Drop-down list.
  5. Click the New button.
  6. Name the control “Desired Outcome”, keep Data type as string, and click OK.
  7. Choose Fixed Values from the Set property value through drop-down.
  8. Click in the boxes for Display Name and Value and type “Desired Increase”, or whatever you want your terminology to be.
  9. Click Add and repeat for “Desired Decrease”.

Use Switch in Visualization Color Expression

So, through our Property Control, we have created a Document Property which will at all times have a value of “Desired Increase” or “Desired Decrease”.  Let’s use it.

  1. Right-click the visualization.
  2. Go to Properties > Colors
  3. Note that you might not have selected which column resides in the Column window–Spotfire auto generates them.  Right-click that column and select Custom Expression.

           Above, we see that my Waterfall Chart created used (Value axis values) by default.  We will make our changes here.

4. You will also note in the image below on the left that the default custom expression is a new column based on the Y.axis–not one from my original dataset.  Spotfire has created it specifically for this visual.

Default Custom Expression                                                                                                                   Customized Custom Expression


           Above, on the right we see how I have customized the expression so that it reacts to the switch.  I created an if statement that will reverse the value with a negative sign if my switch says the desired outcome is a decrease.  I am simply flipping the value.  Since we are in the color window, this ONLY affects the value used for the color not the value for the bars.

Note that the expression also has a “THEN” which the visualization created in order to get the proper display.  You may have different variations of the color expression for different visualizations, but the main thing to focus on is that you are replacing the [value] part with your if statement.  When I performed the same trick on a Tree Map, the value which I replaced with my if statement was wrapped in sum().

5.  While the above expression does only affect the color and not the measured value, the Tooltip  generally displays the Color by value.  To change this, go to Edit > Properties > Tooltip and deselect Color by.   You can also add back the actual value in this window.

So, to review, to make our switch, we create a Control Property that will output a Document Property with either a “Desired Increase” or “Desired Decrease” value.  Then, if we desire a decrease, we place a negative sign before the value in the Color by expression.  Give it a try and let me know if you have any issues.



Leave a Reply

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