Coloring Cross-Table cells based on another column via Property Control

[Vincent Thuilot]

Hello,

In Dr Spotfire June 2022 there's a quick reference about coloring Cross-Table cells based on another column via Property Control.

Would you have an example to share? Could anyone achieve this already?

I am facing the situation where this precisely needs to be working!

Thanks for your support,

Vincent

[Fredrik Rosell]

Hello,

I took a quick look at that Dr. Spotfire session but was unable to locate the specific reference you mentioned. I have reached out to one of the presenters of that Dr. Spotfire session and asked him to follow up.

However, a rather common solution to the "color by another column"-type of use case is to use the "Color the grouping using another expression" functionality. In case that could be applied to your use case, you can find another community question where that was discussed here:

https://community.spotfire.com/s/question/0D54z00007pNOAWCA4/how-to-apply-color-to-a-cell-based-on-the-value-of-that-cell-and-the-value-in-another-column-cell-in-a-cross-table

 

[Vincent Thuilot]

Hello

it was in the Uncommon Authoring Tricks for Spotfire session, at 15.00min

The question was asked but the answer wasn't really detailed.

There is a mention of using a parameter but that's it.

I would like to know a bit more to be able to perform this.

[Fredrik Rosell]

Hello again,

Thank you for the clarification!

For this use case I would definitely look into the example I mentioned in my previous response, using "Color the grouping using another expression" functionality - it sounds like the use case it's designed for.

As an additional example, as that question was about red/orange/green status alerts based on another column, I created this simple example below. In my example, I have a Status column, containing Bad or Good values, that is not displayed in my cross table. I want the Sum(Value) column in my cross table to be red if any of the corresponding Status values were Bad, and green otherwise. In this simple example, I simply count the number of Bad statuses using the expression Sum(If([status]="Bad",1,0)), and then added a rule to color the cell red if this value is > 0.

[Vincent Thuilot]

Hello Fredrik,

thanks for checking, I'm afraid the "Color the grouping using another expression" functionality is only available starting Spotfire 11, is that correct?

We are still using Spotfire X, that's why the solution with a document property was giving me some hope.

It seems that if in the end there is no workaround possible with document property I will have to use Tableau whilst waiting for Spotfire 11.

Thanks!

[Fredrik Rosell]

Hello Vincent,

Correct. To be exact - this feature was introduced in Spotfire 11.2 (released in February 2021): https://community.spotfire.com/s/article/What-s-New-in-TIBCO-Spotfire-11-2#colorcrosstablecellsbyothercolumns - so it is available in all versions currently in full support (11.4 LTS being the oldest of those).

It's easy to color by document properties as well (e.g. see example below, where I color a cell red if the value for the cell is greater than the value of the document property myProp (with the value 14 in this example) , green otherwise), but then you need to figure out what your process should be for getting the values of interest from that other column into the document properties and how to handle that will depend on exactly what you are trying to achieve. I have asked the Dr. Spotfire presenters from the session you referenced if they have any general guidance to offer.

Just so you are aware of it, depending on your use case, one thing that could come in handy is the "Custom DateTime Data Function for TIBCO Spotfire": https://community.spotfire.com/s/article/Custom-DateTime-Data-Function-for-TIBCO-Spotfire

"The main goal of the Custom DateTime Data Function for TIBCO Spotfire® is to enable the running of IronPython scripts on Analysis Open, Filter and Marking changes.". So, that could e.g. be one way to run IronPython scripts that could update your document properties used for coloring.

Best Regards

Fredrik

[Vincent Thuilot]

Thanks a lot Fredrik for the feedback.

I will investigate this interesting case of document properties whilst waiting for the next upgrade of Spotfire.

In your example it's quite straightforward because the Value used is already present in the Cross-Table.

Using a custom expression and calling [Axis Color] does definitely the job.

My challenge currently is that the data I would like to use is outside the cross-table (but in the same table), and it's a categorical column containing either "Priority Segment" or "Opportunistic". Let's call this column "Matrix".

If I use a document property I can't refer to the Matrix column there, it's restricted to the value inside the cross-table.

See attached what I have.

[Fredrik Rosell]

Hello again Vincent,

Just FYI: I talked to the Dr. Spotfire hosts, and they did not have any additional suggestions for you.

Getting your data into the document properties definitely sounds like the tricky part. I can only suggest looking into what you can do with setting those either in manually triggered IronPython scripts or automatically triggered, e.g. using the Custom DateTime Data Function I mentioned.

Hopefully you'll upgrade soon so the easy way of doing this becomes available to you.

 

[Vincent Thuilot]

Thanks, it's avoiding me hours of research!