Jump to content
We've recently updated our Privacy Statement, available here. ×

Conditional coloring with "First" and "Second" in the rule expression

Tim McCune 3

Recommended Posts

I have a table where the first row in the table is the lower limit for the column and the 2nd row in the column is the upper limit for the column. I want to color the values in the each column to indicate the values that fall outside the columns limits.

To color the values that fall below the lower limit, I can just use this expression:

[Col1] < First([Col1])

To highlight values above the upper limit, I would like something like this:

[Col1] > Second([Col1])

But of course, this "Second" function doesn't exist.

I thought it might be possible to do something like this:

[Col1] > select [Col1] where RowId()=2

but there doesn't seem to be a "select" functon or it's equivalent.

It seems strange that aprogram based on databases doesn't support SQL queries, as far as I can tell.

Does anyone know how to do this

Link to comment
Share on other sites

I presume you are referring to Spotfire. You cannot refer to another row by its position, but there is a possible workaround.

Please note useful tips for asking questions here.




A - Create two calculated columns:

[Lower] defined as:

case [unit]

when Lower Limit" then "X"

else NULL end

[upper] defined as:

case [unit]

when Upper Limit then X"

else NULL end

It does not matter what value they have as long as they only have a value for the corresponding row, and null otherwise. You dont need to display these columns.


B - for each of your columns Col1, Col2 etc, define the colouring by adding two boolean expressions in turn. To do this, in the colouring tab, add colouring by, say, Col1, then click on Add Rule, choose Rule Type = Boolean Expression, then click on Value to edit Custom Expression, and type

[Col1] < ValueForMax([Lower],[Col1])

This is your rule for colouring the lower values.

Then add another rule as:

[Col1] > ValueForMax([upper],[Col1])

This is your rule for colouring the upper values.

ValueForMax(..) ensures you are selecting the value of [Col1] corresponding to the max value of either [Lower] or [upper], and the max value of a column with all nulls apart from one value is that value.

The result should look like the attached picture.

Link to comment
Share on other sites

For what it's worth, here's a script I'm working on to automatically color values in a table that fall outside their column limits.  This is still a work in progress, for instance, I have a global lower limit where I'll really to have specific limits for each column, which I'll probably ending up reading from a separate limits table.  And I haven't implemented the upper limit yet either, but I got the core of it worked out yesterday. from Spotfire.Dxp.Application.Visuals import *

from System.Drawing import *for p in Document.Pages:

    if p.Title == "Page1":

        page = p

        breakfor v in page.Visuals:

    if v.Title == "TablePlot1":

        vis = v.As[TablePlot]()


columnsToExclude = ["Unit"]

lowerLimit = 40for c in vis.Columns:

    colName = c.DataColumnName


    if not columnsToExclude.Contains(colName):

        coloring = vis.Colorings.AddNew(colName)

        coloring.DefaultColor = Color.White

        coloring.EmptyColor = Color.White        coloring.AddExpressionRule('[' + colName + '] < ' + str(lowerLimit), Color.FromName("LightBlue"))

        vis.Colorings.AddMapping(CategoryKey(colName), coloring)


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...