You may already be aware that you can configure the X and Y
axis to use a predefined expression, rather than the raw data values from a
column. This expression can perform complex aggregations, calculations between
one column and other columns in the data table, or logical calculations based
off the values in particular column(s).
What you may not be aware of is that you can use the same
expression syntax in other axis, like the Color axis, or Size axis. This becomes very valuable when you are
trying to color or size, for example, on something that is not built into the
raw data.
Let’s assume you have a Bar Chart that shows a distribution
of values:
You can choose to color based off another categorical column in
your data set, but that may not be what you want to display in this chart. If
you have many bars, you may want to color bars to put them into different bins,
“Low”, “Normal”, and “High” or similar.
You can do this in a variety of ways. The way I see most
people do this is use the Tag Panel to create a Tag Collection , and then
identify the different bars (in this case Z-scores) as either “Low”, “Normal”,
or “High”. (We will discuss Tags in a future Post and they are also discussed
in detail in both SP151 TIBCO Spotfire Distributing Analytics and Data Cleaning, Transforming, and Pre-processing in TIBCO Spotfire).
That will work, and the benefit is you can now use those tags
in another chart if needed. The downside is any new data loaded may not be
applied to your tags as you want them to be.
However another option, which will get executed against new
or replaced data, is to use an expression
directly in the color-axis. To do this
right-click on the color axis selector and select ‘Custom Expression…”
You can then enter any expression you want. In this particular case, we want to color bar
into three segments, based off values from the x-axis column. You can use values from any column, we just
happen to use the x-axis column here to keep it simple. The following is the expression used:
<case when ([Z-score])>(2.0) then
"High" when ([Z-score])<(-2.0) then "Lo" else
"Normal" end>
This expression will check the x-axis value and will identify
the bars with values over 2 as “High”, the bars with values under -2 as “Low”
and everything else as “Normal”. The names used to display inside the color
selector shown in the legend.
If you need to you can now adjust the colors for the three
segments in the properties dialog.
The expression will now be re-calculated to update the
colorings when needed (when new data is added, when filtering is performed,
etc…)
Expressions can also be used in the size axis. Although this expression is much more
complicated, the following expression is used in the size-axis to size a marker
based off the number of highly discounted items.
This expression, as well as the entire concept of
Expressions, is discussed and explained in detail in the training course SP141
: TIBCO Spotfire Computational Analytics and the webinar Custom Expressions in TIBCO Spotfire