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

[Tim McCune 3]

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

[Gaia Paolini]

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.

https://community.spotfire.com/wiki/quick-tips-asking-community-questions

Gaia

 

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.

[Tim McCune 3]

Thank you Gaia, that's a very nice technique.  I like how the calculated columns act as pointers to the data column's limit row value. I didn't know about the "ValueForMax" function, I'll add that to my toolbox :-)

[Tim McCune 3]

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]()

        breakvis.Colorings.Clear()

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)